MySQL query in PHP: Not query zero amounts also removes null amounts from query 
Author Message
 MySQL query in PHP: Not query zero amounts also removes null amounts from query

Hello,

I have a MySQL query in PHP (I've simplified it):

$sql = "SELECT * FROM table WHERE customer_number = 5000 receipt_value <> 0 ";

Now I want to exclude records with a receipt_value of 0.00 (receipt_value
field is a double data type).

*However* I do want to include records where receipt_value is null (the field
allows for null values), this is important.

However, the query also removes records where receipt_value is null as well as
ones with 0 value (which we want).

How do I create the query to include null values of receipt_value? For reasons
I won't go into, I want to do this at the query level if possible.

Cheers,
Lee.



Tue, 19 Jul 2005 18:46:17 GMT  
 MySQL query in PHP: Not query zero amounts also removes null amounts from query

Quote:

> I have a MySQL query in PHP (I've simplified it):

> $sql = "SELECT * FROM table WHERE customer_number = 5000 receipt_value <>
0 ";

> Now I want to exclude records with a receipt_value of 0.00 (receipt_value
> field is a double data type).

> *However* I do want to include records where receipt_value is null (the
field
> allows for null values), this is important.

Hi Lee,

How about:

$sql = "SELECT * FROM table WHERE customer_number = 5000 AND (receipt_value
IS NULL OR receipt_value <> 0.00)";

HTH;
JOn



Tue, 19 Jul 2005 19:20:55 GMT  
 MySQL query in PHP: Not query zero amounts also removes null amounts from query

Quote:


>> I have a MySQL query in PHP (I've simplified it):

>> $sql = "SELECT * FROM table WHERE customer_number = 5000 receipt_value <>
>0 ";

>> Now I want to exclude records with a receipt_value of 0.00 (receipt_value
>> field is a double data type).

>> *However* I do want to include records where receipt_value is null (the
>field
>> allows for null values), this is important.

>Hi Lee,

>How about:

>$sql = "SELECT * FROM table WHERE customer_number = 5000 AND (receipt_value
>IS NULL OR receipt_value <> 0.00)";

>HTH;
>JOn

Thanks Jon, that works, seems obvious now :)

Cheers,
Lee.



Tue, 19 Jul 2005 20:24:59 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Outfile in mySQL Query using PHP

2. Problem using MULTIPLE FUNCTIONS in mySQL/php query

3. mysql + mxdatetime query problem--datetime not returned sometimes

4. clipper query conver to sql query

5. PHP not sending an SQL Query properly

6. Yet Another PEP: Query Protocol Interface or __query__

7. Yet Another PEP: Query Protocol Interface or __query__

8. Any help on converting a dollar amount to a written amount, for checks

9. Novice Query on NULL

10. query regarding NULL result without error in COM code

11. removing SQL query string

12. problem with threading mysql queries in ruby

 

 
Powered by phpBB® Forum Software