[PHP-DB] query problem..

[Micah Stevens]

Hi,

I'm getting an unknown colum `num` in where clause error with this query:

$options = mysql_query(select options.*, 
count(option_items.optionID) as `num` 
from options
 left join option_links using (optionID)
left join option_items on options.optionID = 
option_items.optionID
 where option_links.productID = '$productID'
and `num`  0);

But I'm defining it with the agregate function in the second line.. Any idea 
why it's popping the error?

-Micah

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query Problem

[Larry Sandwick]

I need to sum the field *COST* in this query where data in *STATUS* is
equal to HELD and OPEN, so I will have 2 totals passed below and do
not know where to begin .

 

All information is in 1 table.

 

Original query

select distinct(Company_name), account, City, State from table where
number = 100

 

I need to be able to pull the $$ amount totals by company name for
status equal to HELD and OPEN .

 

Result 

 

 Company A  OPEN   HELD

 

 1233100.0050.00

 

 

Any suggestion would be appreciated.

 

 

 

Larry Sandwick

 

 

Larry Sandwick

Sarreid, Ltd.

Network/System Administrator

phone: (252) 291-1414 x223

fax  : (252) 237-1592

 

[PHP-DB] query problem

[Doug Parker]

I'm having a problem with a specific query.

SELECT DISTINCT(contents_.company_id), companies.* FROM contents_, companies
WHERE contents_.Products LIKE '%heating%'
OR contents_.Manufacturer LIKE '%heating%'
AND contents_.company_id = companies.id


The query works fine without the OR segment, for example:

SELECT DISTINCT(contents_.company_id), companies.* FROM contents_, companies
WHERE contents_.Products LIKE '%heating%'
AND contents_.company_id = companies.id

works fine.  But I need the query to search 2 columns, and it's returnng a
vast number of records when I do the first search.

Any help would be greatly appreciated.


http://www.phreshdesign.com

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

AW: [PHP-DB] query problem

[Lars Jedinski]

I'm not sure if you mean that, but try:

SELECT DISTINCT(contents_.company_id), companies.* FROM 
contents_, companies
WHERE (contents_.Products LIKE '%heating%'
OR contents_.Manufacturer LIKE '%heating%')
AND contents_.company_id = companies.id

Lars

 -Ursprüngliche Nachricht-
 Von: Doug Parker [mailto:[EMAIL PROTECTED] 
 Gesendet: Donnerstag, 2. Oktober 2003 20:31
 An: [EMAIL PROTECTED]
 Betreff: [PHP-DB] query problem
 
 
 I'm having a problem with a specific query.
 
 SELECT DISTINCT(contents_.company_id), companies.* FROM 
 contents_, companies
 WHERE contents_.Products LIKE '%heating%'
 OR contents_.Manufacturer LIKE '%heating%'
 AND contents_.company_id = companies.id
 
 
 The query works fine without the OR segment, for example:
 
 SELECT DISTINCT(contents_.company_id), companies.* FROM 
 contents_, companies
 WHERE contents_.Products LIKE '%heating%'
 AND contents_.company_id = companies.id
 
 works fine.  But I need the query to search 2 columns, and 
 it's returnng a
 vast number of records when I do the first search.
 
 Any help would be greatly appreciated.
 
 
 http://www.phreshdesign.com
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query problem

[Ron Allen]

I have done this in the past, but for some reason it isn't working now
(maybe a moron).  I am trying to select all of the empty or NULL fields in a
column.

This is what I have
select * from ticket where Type = 'Line' and Closeddate = ' empty space'
and have tried the following
select * from ticket where Type = 'Line' and Closeddate = 'Null'
select * from ticket where Type = 'Line' and Closeddate = 'NULL'

any clues 



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Query problem

[Dirk Kredler]

Am Dienstag, 22. Juli 2003 11:29 schrieb Ron Allen:
 This is what I have
 select * from ticket where Type = 'Line' and Closeddate = ' empty space'
 and have tried the following
 select * from ticket where Type = 'Line' and Closeddate = 'Null'
 select * from ticket where Type = 'Line' and Closeddate = 'NULL'

use

isNULL(Closedate) instead of Closedate = 'NULL'



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] Query problem

[jeffrey_n_Dyke]

you can't use COL_NAME = NULL or COL_NAME = 'NULL' and get the desired
results , you have to check for IS NULL/ IS NOT NULLSo, change the
query to

SELECT * FROM TABLE WHERE MY_FIELD IS NULL  or,
SELECT * FROM TABLE WHERE MY_FIELD IS NOT NULL

From my understanding when you check for  COL_NAME = 'Null' you're actually
checking to see if your field contains a _string_  'Null'

hth
jeff




   
 
  Boaz Yahav 
 
  [EMAIL PROTECTED]To:   Ron Allen [EMAIL 
PROTECTED], [EMAIL PROTECTED]   
  .net.il cc: 
 
   Subject:  RE: [PHP-DB] Query problem
 
  07/22/2003 03:16 
 
  PM   
 
   
 
   
 




Assuming you run MySQL, why don't you just remove the  ' ?

SELECT * FROM MyTable WHERE MyField=NULL

Sincerely

berber

Visit http://www.weberdev.com/ Today!!!
To see where PHP might take you tomorrow.
Share your code : http://addexample.weberdev.com


-Original Message-
From: Ron Allen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 22, 2003 11:29 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query problem


I have done this in the past, but for some reason it isn't working now
(maybe a moron).  I am trying to select all of the empty or NULL fields
in a column.

This is what I have
select * from ticket where Type = 'Line' and Closeddate = ' empty space'
and have tried the following select * from ticket where Type = 'Line'
and Closeddate = 'Null' select * from ticket where Type = 'Line' and
Closeddate = 'NULL'

any clues 



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php






-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] query problem

[Sommai Fongnamthip]

Hi,
I have problem with these mysql's query:

select * from holder, management where holder.id=management.id or 
(holder.name=management.name and holder.surname=management.surname) order 
by holder.no

It take a long time (more than 1 minute) with thousand record.  I have 
index within 2 table (name+surname, id).  It did not have problem if I 
check only id or name+surname.

SF


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DB] query problem

[Jason Wong]

On Tuesday 22 January 2002 13:18, Sommai Fongnamthip wrote:

 Hi,
   I have problem with these mysql's query:

   select * from holder, management where holder.id=management.id or
 (holder.name=management.name and holder.surname=management.surname) order
 by holder.no

   It take a long time (more than 1 minute) with thousand record.  I have
 index within 2 table (name+surname, id).  It did not have problem if I
 check only id or name+surname.

This is a mysql question. Perhaps you should ask on the mysql list.


-- 
Jason Wong - Gremlins Associates - www.gremlins.com.hk

/*
One expresses well the love he does not feel.
-- J.A. Karr
*/

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] Query problem cont'd..

[Russ Michell]

Hi there:

I'm stil having problems with this query in that it doesn't bring up any results!
Here is the select menu that refreshes the page and deposites the var: 'sortedBy' 
into play:

form
select name=\sortMonth\ onChange=\MM_jumpMenu('parent',this,0)\;
option--select one--/option
option value=\$PHP_SELF?sortedBy=Jan\Jan/option
option value=\$PHP_SELF?sortedBy=Feb\Feb/option
option value=\$PHP_SELF?sortedBy=Mar\Mar/option
option value=\$PHP_SELF?sortedBy=Apr\Apr/option
option value=\$PHP_SELF?sortedBy=May\May/option
option value=\$PHP_SELF?sortedBy=Jun\Jun/option
option value=\$PHP_SELF?sortedBy=Jul\Jul/option
option value=\$PHP_SELF?sortedBy=Aug\Aug/option
option value=\$PHP_SELF?sortedBy=Sep\Sep/option
option value=\$PHP_SELF?sortedBy=Oct\Oct/option
option value=\$PHP_SELF?sortedBy=Nov\Nov/option
option value=\$PHP_SELF?sortedBy=Dec\Dec/option
/select
/form

The SQL qery:

if(
($sortedBy == 'Jan') || ($sortedBy == 'Feb') || ($sortedBy == 'Mar') || 
($sortedBy == 'Apr') || ($sortedBy == 'May') || ($sortedBy == 'Jun') ||
($sortedBy == 'Jul') || ($sortedBy == 'Aug') || ($sortedBy == 'Sep') || 
($sortedBy == 'Oct') || ($sortedBy == 'Nov') || ($sortedBy == 'Dec')
) 
{
$sql = SELECT * FROM $table_cal WHERE item_activity='$id' AND 
DATE_FORMAT('item_date','%b')='$sortedBy'; 
}

Thanks to Rasmus for helping me out so far!
Can anyone see what I may be doing wrong???

I'm using mysql-3.22.32 and the item_date column is a MySQL DATE() column..

Cheers:
Russ

#---#

 Believe nothing - consider everything
   Web Developers do it on-the-fly.

  Russ Michell
  Anglia Polytechnic University Webteam
  
  e: [EMAIL PROTECTED]
  w: www.apu.ac.uk/webteam
  t: +44 (0)1223 363271 x 2331

  www.theruss.com

#---#


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

RE: [PHP-DB] Query problem cont'd..

[Steve Brett]

ok. try this.
ahven't tested it though ...

 form name=thisForm action = ?php print $PHP_SELF ?
 
?php

// to dump out the months
$months = Array(January, February, March, April, May, June,
July, August, September, October, November, December);
echo 'select name=sortMonth onChange=document.thisForm.Submit()';
for ($x=1; $x = count($months); $x++)
{
print \toption value=\$x\ ;
print ($x == $month) ?  SELECTED: ;
print .$months[$x-1].\n;
}
echo ' /select ';

?

then something like (pref before the dump of months)

?php

if (isset($sortMonth))
{
 $sql = SELECT * FROM $table_cal WHERE item_activity='$id' AND 
 DATE_FORMAT('item_date','%b')='$sortMonth';

// then the rest of the code for the query

}


the real crux is that the var whihc is the name of the select will be
populated when the form refreshes and then isset() will say it has a value
and drop inside to create the query. that way you avoid an expensive if or
or or or ...



Steve 

 -Original Message-
 From: Russ Michell [mailto:[EMAIL PROTECTED]]
 Sent: 26 April 2001 10:50
 To: [EMAIL PROTECTED]
 Subject: [PHP-DB] Query problem cont'd..
 
 
 Hi there:
 
 I'm stil having problems with this query in that it doesn't 
 bring up any results!
 Here is the select menu that refreshes the page and 
 deposites the var: 'sortedBy' into play:
 

 option--select one--/option
 option value=\$PHP_SELF?sortedBy=Jan\Jan/option
 option value=\?sortedBy=Feb\Feb/option
 option value=\$PHP_SELF?sortedBy=Mar\Mar/option
 option value=\$PHP_SELF?sortedBy=Apr\Apr/option
 option value=\$PHP_SELF?sortedBy=May\May/option
 option value=\$PHP_SELF?sortedBy=Jun\Jun/option
 option value=\$PHP_SELF?sortedBy=Jul\Jul/option
 option value=\$PHP_SELF?sortedBy=Aug\Aug/option
 option value=\$PHP_SELF?sortedBy=Sep\Sep/option
 option value=\$PHP_SELF?sortedBy=Oct\Oct/option
 option value=\$PHP_SELF?sortedBy=Nov\Nov/option
 option value=\$PHP_SELF?sortedBy=Dec\Dec/option
 /select
 /form
 
 The SQL qery:
 
 if(
 ($sortedBy == 'Jan') || ($sortedBy == 'Feb') || ($sortedBy == 
 'Mar') || 
 ($sortedBy == 'Apr') || ($sortedBy == 'May') || ($sortedBy == 
 'Jun') ||
 ($sortedBy == 'Jul') || ($sortedBy == 'Aug') || ($sortedBy == 
 'Sep') || 
 ($sortedBy == 'Oct') || ($sortedBy == 'Nov') || ($sortedBy == 'Dec')
 ) 
 {
 $sql = SELECT * FROM $table_cal WHERE item_activity='$id' AND 
 DATE_FORMAT('item_date','%b')='$sortedBy'; 
 }
 
 Thanks to Rasmus for helping me out so far!
 Can anyone see what I may be doing wrong???
 
 I'm using mysql-3.22.32 and the item_date column is a MySQL 
 DATE() column..
 
 Cheers:
 Russ
 
 #---#
   
  Believe nothing - consider everything  
Web Developers do it on-the-fly.
   
   Russ Michell
   Anglia Polytechnic University Webteam
   
   e: [EMAIL PROTECTED]
   w: www.apu.ac.uk/webteam
   t: +44 (0)1223 363271 x 2331
 
   www.theruss.com
   
 #---#
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]
 To contact the list administrators, e-mail: 
 [EMAIL PROTECTED]
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] query problem

[Russ Michell]

Hi all:

I'm using mysql-3.22.32:

My PHP-embedded query is as follows:

else if($sortBy == '01') {
$sql = SELECT * FROM $table_cal WHERE item_activity='$id' AND 
DATE_FORMAT('item_date','%m') LIKE '$sortBy'; 
$sortBy = Month of January;
}

* '$sortBy' is equal to '01'.
* MySQL isn't complaining about any of the query.
* DATE_FORMAT('item_date','%m') should be '01' (taken from 
a 'date' column in -MM-DD fromat.

Can anyone tell me what is wrong with this query (I may have missed 
something - but then that shouldn't surprise as I've been staring at it 
for 3 hours...;-)

Cheers:
Russ

#---#

 Believe nothing - consider everything
   Web Developers do it on-the-fly.

  Russ Michell
  Anglia Polytechnic University Webteam
  
  e: [EMAIL PROTECTED]
  w: www.apu.ac.uk/webteam
  t: +44 (0)1223 363271 x 2331

  www.theruss.com

#---#


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]