Checking condition in SQL Query

[theITvideos]

Hi there,

 

I am working on a PHP website. And I am running a query which checks for the expired products.

 

We check to see if the expiry date is greater than the current systems date then display the record. It looks something like this:

 

select * from products WHERE products.productExpiry) >= '2010-10-13 00:00:00' 

 

'productExpiry' field is of 'Datetime' Datatype

 

And Expiry date is stored in database as:

 

2010-10-19 04:39:35

 

Now it works fine. But the problem only arises (no record shows)  when there is no date in the 'productExpiry' field.

 

How can I check or set a condition in my SQL query that whenever the 'productExpiry' is Null or Blank don't compare it with the current date.

 

Please reply

 

All comments and feedbacks are welcomed

 

Thank you!

 

 

 

[Pikachu2000]

I hope this is what you're referring to . . . To also select the records that have an empty value (which isn't the same as NULL) add: OR productExpiry = ''. For NULL values, add: OR productExpiry IS NULL.

[Psycho]

I hope you are not setting the date in the query using PHP. Just use NOW() in your query.

 

Anyway, to include records with NULL values you could do this:

SELECT *
FROM `products`
WHERE products.productExpiry >= NOW()
   OR products.productExpiry IS NULL

 

Although you may need to normalize NOW() to be midnight of the current date.

[theITvideos]

I hope you are not setting the date in the query using PHP. Just use NOW() in your query.

 

Anyway, to include records with NULL values you could do this:

SELECT *
FROM `products`
WHERE products.productExpiry >= NOW()
   OR products.productExpiry IS NULL

 

Although you may need to normalize NOW() to be midnight of the current date.

 

Thank you it worked!

[theITvideos]

I hope you are not setting the date in the query using PHP. Just use NOW() in your query.

 

Anyway, to include records with NULL values you could do this:

SELECT *
FROM `products`
WHERE products.productExpiry >= NOW()
   OR products.productExpiry IS NULL

 

Although you may need to normalize NOW() to be midnight of the current date.

 

Thank you it worked!

 

Thanks for the solution you suggested. Now I am running a query which also searches for the Product Description.

 

SELECT *
FROM products
WHERE productDescription LIKE '%testProd% AND productExpiry >= NOW()
   OR productExpiry IS NULL

 

And in the search when I type 'testProd' it returns 'testProd but also all the rows having productExpiry equal to NULL.

 

Is there a way we can make the SQL Query to search for the ProductName beginning with 'testProd' AND not return all the other rows with ProductExpiry equal to NULL. AND also at the same time check the productExpiry >= NOW()

 

Kindly reply.

 

Thank you!

[Psycho]

Don't use "*" in your query - specify the fields you want.

 

You need to enclose conditions in quotes. The way you have it written will bring up all records where

description matches and date greater than now OR date is null

 

What you want is

description matches AND (date greater than now or date is null)

SELECT *
FROM products
WHERE productDescription LIKE '%testProd%
  AND (productExpiry >= NOW()   OR productExpiry IS NULL)

[theITvideos]

Don't use "*" in your query - specify the fields you want.

 

You need to enclose conditions in quotes. The way you have it written will bring up all records where

description matches and date greater than now OR date is null

 

What you want is

description matches AND (date greater than now or date is null)

SELECT *
FROM products
WHERE productDescription LIKE '%testProd%
  AND (productExpiry >= NOW()   OR productExpiry IS NULL)

 

AWESOMEEE!!!

 

I Love this forum!!

 

A big thank you to you bro!