PHP SQL query with DATE_SUB returns no result
Posted: Fri Apr 11, 2008 10:22 am
I'm trying to produce a query that deletes all shopping carts that have had no products added to them for a number of days specified by $days. Even if I set $days to zero no results are returned.
Here is the code:
$sql = "SELECT cart_id, date_product_added
FROM shopping_cart
GROUP BY cart_id
HAVING DATE_SUB(CURDATE(), INTERVAL $days DAY)
>= MAX(date_product_added)";
$query = mysql_query($sql);
$result = mysql_num_rows($query);
As I understand it the DATE_SUB method will return the date that is the result of CURDATE() - NUM_OF_DAYS. This means that if $days is equal to zero than the date returned would be the current date.
If the date_product_added was two hours earlier then the current date then the query should yield a result that is not zero. This is because the date returned by DATE_SUB() is greater than or equal to MAX(date_product_added). MAX returns the date with the highest value in the shopping cart.
Why is this query returning 0 results even when I set $days to zero.
Thanks for any help.
Here is the code:
$sql = "SELECT cart_id, date_product_added
FROM shopping_cart
GROUP BY cart_id
HAVING DATE_SUB(CURDATE(), INTERVAL $days DAY)
>= MAX(date_product_added)";
$query = mysql_query($sql);
$result = mysql_num_rows($query);
As I understand it the DATE_SUB method will return the date that is the result of CURDATE() - NUM_OF_DAYS. This means that if $days is equal to zero than the date returned would be the current date.
If the date_product_added was two hours earlier then the current date then the query should yield a result that is not zero. This is because the date returned by DATE_SUB() is greater than or equal to MAX(date_product_added). MAX returns the date with the highest value in the shopping cart.
Why is this query returning 0 results even when I set $days to zero.
Thanks for any help.