Deleting after 7 days?

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

User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Deleting after 7 days?

Post 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
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post by wyred »

Sounds like you need a CronJob!

But that's only if your webserver is running on Linux.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

there are also some advanced task schedulers for windows as well as the in-built system
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Isn't that the purpose of the logs? :)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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! :)
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Any ideas?
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Any ideas what's wrong?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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;);
}
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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());
Post Reply