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.
PHP SQL query with DATE_SUB returns no result
Moderator: General Moderators
- 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
You use a cron job
Re: PHP SQL query with DATE_SUB returns no result
I don't know what a cron job is
- 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
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.
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
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
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
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.
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
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.
It's quite simple if your familar with MySQL methods, but I'm new to them.