MySQL CURDATE

PHP
1

Hi all, trying to display all the products I have added today by using CURDATE but does not seem to be returning any results. I know the issue is in my query. Please see below. Any help would be appreciated.

$query = "SELECT
    products.products_id,
    products_description.products_name,
    products.products_status,
	products.products_price,
	products.used_condition,
	products.actuations
	FROM products
	INNER JOIN products_description
	ON products.products_id = products_description.products_id WHERE products.products_date_added = CURDATE()";
2

you did not explain how you populate your products_date_added column, so i will guess that it’s either DATETIME or TIMESTAMP, but not DATE

use this –


WHERE products_date_added >= CURRENT_DATE
  AND products_date_added  < CURRENT_DATE + INTERVAL 1 DAY

this sets up a range test, where the low end of the range is today at midnight this morning, while the upper end of the range is tonight at midnight (actually tomorrow morning at midnight), and the upper end is not included in the range

3

it’s analagous to the way you would check for products added this year…


WHERE products_date_added >= '2010-01-01'
  AND products_date_added  < '2011-01-01'

see how the range works?

yes, you could use this –

WHERE YEAR(products_date_added) = 2010

but that form cannot be optimized

the range test allows an index to be used

similarly, for today’s products, you could use the DATE function to strip off the time–

WHERE DATE(products_date_added) = CURRENT_DATE

but this would not be efficient

use the range test :slight_smile:

4

Bingo! That’s done the trick. Thank you for explainin, it’s good to have an understand of what the problem was rather than just a solutution/fix so thank you once again. Good day to you.