Page 1 of 2

Deleting after 7 days?

Posted: Tue Jun 07, 2005 3:07 am
by Mr Tech
Hey there,

I have created a shopping cart. When someone adds items to the cart it logs their order id and the time(using the time() function) they added the items.

The problem is when someone adds items to their cart and does not check out, the order is left sitting in the database... Over time this will be a lot of orders cloggin the database with out of date data...

How do I check the MYSQL database for entries over 7 days old and then delete them?

Thanks

Ben

Posted: Tue Jun 07, 2005 3:10 am
by wyred
Sounds like you need a CronJob!

But that's only if your webserver is running on Linux.

Posted: Tue Jun 07, 2005 3:13 am
by malcolmboston
there are also some advanced task schedulers for windows as well as the in-built system

Posted: Tue Jun 07, 2005 3:33 am
by Mr Tech
I don't think I need a cron job... I'll just set the code when someone visits the shopping cart...

So does anyone know of some code?

Posted: Tue Jun 07, 2005 5:15 am
by timvw
something like (Read the mysql manual chapter 12.5 for date and time functions)

Code: Select all

DELETE 
FROM order
WHERE status='Uncomplete'
AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) <= time

Posted: Tue Jun 07, 2005 5:31 am
by CoderGoblin
You may want to consider moving the "unwanted" orders into a "backup table" rather than just deleting them in case you ever want statistics. A "high" number of uncompleted orders is generally a sign of the order process being too complicated.

Posted: Tue Jun 07, 2005 6:18 am
by timvw
Isn't that the purpose of the logs? :)

Posted: Tue Jun 07, 2005 6:32 am
by CoderGoblin
Depends on what you store in the logs. I often find it easier to get the information which is already stored in the database rather than process log files.

Posted: Wed Jun 08, 2005 12:45 am
by Mr Tech
timvw wrote:something like (Read the mysql manual chapter 12.5 for date and time functions)

Code: Select all

DELETE 
FROM order
WHERE status='Uncomplete'
AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) <= time
Thanks timvw...

So what do I need to replace in your code and with what? The ones I think I need to replace are:

CURDATE(), INTERVAL 7 DAY and time

So I'm guessing:

CURDATE() is the current time and date.
INTERVAL 7 DAY is the time it was 7 days ago.
time is the date it was created?

Is DATE_SUB a function?

Am I right with everythign above ?

Thanks for your help! :)

Posted: Wed Jun 08, 2005 6:11 pm
by Mr Tech
Any ideas?

Posted: Wed Jun 08, 2005 7:12 pm
by andre_c
actually you only need to replace:
- time: date in which the order was made
- status: in case it's different in your system

everything else should stay the way timvw wrote it

Posted: Wed Jun 08, 2005 8:03 pm
by Mr Tech
Thanks andre.

I'm just testing the code and it seems to be bringing up results from about a day ago... My code:

Code: Select all

$query = mysql_query("SELECT orderid,time FROM {$tbl_name}products_orders WHERE bought='no' AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) <= time");
while($row = mysql_fetch_array($query)) {
echo $row[orderid]." ". date('d-M-Y', $row[time]) ." ";
}
And it came up with one from 07-Jun-2005 and another from 06-Jun-2005...

Any ideas what is wrong?

Posted: Thu Jun 09, 2005 7:39 pm
by Mr Tech
Any ideas what's wrong?

Posted: Mon Jun 13, 2005 10:50 pm
by Mr Tech
Well, it never ended up working but I found another way. It probably isn't as good but atleast it works:

Code: Select all

$days = 7*24*60*60; // How many days till remove...
$now = time(); 
$expired = $now - $days; 

$query = mysql_query(&quote;SELECT orderid,time,bought FROM {$tbl_name}products_orders WHERE bought='no' AND time <= $expired&quote;) or die(mysql_error());
while($row = mysql_fetch_array($query)) {
mysql_query(&quote;delete from {$tbl_name}products_orders where orderid='$row&#1111;orderid]'&quote;);
}

Posted: Tue Jun 14, 2005 3:00 am
by timvw
You can do this with a single query too...

Code: Select all

$days = 7*24*60*60; // How many days till remove...
$now = time(); 
$expired = $now - $days; 
 
$sql = <<<SQL
DELETE 
FROM {$tabl_name}products_orders
WHERE bought='no'
AND time <= $expired
SQL;

mysql_query($sql) or die(mysql_error());
And meaby even nicer

Code: Select all

$sql = <<<SQL
DELETE 
FROM {$tabl_name}products_orders
WHERE bought='no'
AND time <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))
SQL;

mysql_query($sql) or die(mysql_error());