PHP SQL query with DATE_SUB returns no result

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
robseek
Forum Newbie
Posts: 7
Joined: Fri Apr 11, 2008 10:19 am

PHP SQL query with DATE_SUB returns no result

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP SQL query with DATE_SUB returns no result

Post by aceconcepts »

You use a cron job
robseek
Forum Newbie
Posts: 7
Joined: Fri Apr 11, 2008 10:19 am

Re: PHP SQL query with DATE_SUB returns no result

Post by robseek »

I don't know what a cron job is
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP SQL query with DATE_SUB returns no result

Post 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.
robseek
Forum Newbie
Posts: 7
Joined: Fri Apr 11, 2008 10:19 am

Re: PHP SQL query with DATE_SUB returns no result

Post 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
robseek
Forum Newbie
Posts: 7
Joined: Fri Apr 11, 2008 10:19 am

Re: PHP SQL query with DATE_SUB returns no result

Post 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.
robseek
Forum Newbie
Posts: 7
Joined: Fri Apr 11, 2008 10:19 am

Re: PHP SQL query with DATE_SUB returns no result

Post 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.
Post Reply