Deleting after 7 days?
Moderator: General Moderators
Deleting after 7 days?
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
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
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
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- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Thanks timvw...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
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!
Thanks andre.
I'm just testing the code and it seems to be bringing up results from about a day ago... My code:
And it came up with one from 07-Jun-2005 and another from 06-Jun-2005...
Any ideas what is wrong?
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]) ." ";
}Any ideas what is wrong?
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("e;SELECT orderid,time,bought FROM {$tbl_name}products_orders WHERE bought='no' AND time <= $expired"e;) or die(mysql_error());
while($row = mysql_fetch_array($query)) {
mysql_query("e;delete from {$tbl_name}products_orders where orderid='$rowїorderid]'"e;);
}You can do this with a single query too...
And meaby even nicer
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());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());