Page 1 of 1

PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 10:22 am
by robseek
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.

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 10:34 am
by aceconcepts
You use a cron job

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 10:47 am
by robseek
I don't know what a cron job is

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 10:59 am
by aceconcepts
A cron job allows you to run/execute a script at a given time.

Within the job you can code as normal.

Using your preferred search engine search for cron jobs - otherwise continue to do it the way you started out.

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 11:16 am
by robseek
I don't want to automate any tasks. I'm building an e-commerce site that has a customised shopping cart. I have attempted to create a query that deletes all the shopping carts that hasn't had any products added within a number of days.

To delete the administrator selects a number of days in drop down list and clicks a delete button. The number of days is held in the $days variable.

I want to know why it's not generating any results even when I set $days to zero. Is it something to do with CURDATE(), MAX, etc

Thanks for any help

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 12:37 pm
by robseek
After hours of searching I think I have found the problem. The date values returned by DATE_SUB and MAX are not compatible. This means when the >= operator is used the result will always be zero because they can't be compared.

I see 2 solutions to this problem:
1) Change the data type in the database from datetime to date
2) Try to cast the result of MAX() to date in the query

I want the time products were added to the cart so the first solution is not ideal. If it's possible I prefer casting in the query.

Re: PHP SQL query with DATE_SUB returns no result

Posted: Fri Apr 11, 2008 1:08 pm
by robseek
I found an ideal solution. I used now() instead of CURDATE() within the DATE_SUB method. This makes the DATE_SUM return datetime, which is what MAX returns. MAX(column) returns the datetime with the highest value. This means the two values can be compared with >=.

It's quite simple if your familar with MySQL methods, but I'm new to them.