Hi guys,

I have a database that usually has around 3000 entries. New entries are entered each day, and I want to set up a cron job to delete old entries. Each entry has a timestamp and I want to delete anything that is at least 24 hours old.

What is the most efficient way to do this? I can think of 2 options.

OPTION 1:
$row = mysql_query("SELECT * FROM table");
foreach($row as $row){
//if $row['timestamp'] is older than 24 hours --> DELETE
}

OPTION 2: is this possible? better?
DELETE * FROM table WHERE currenttime - timestamp > 24 hours

Thanks guys!!

Don't even *THINK* about OPTION 1. Horrible practice.

Several ways to do that.

Look in the MySQL docs.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

DELETE FROM tablename WHERE TIMESTAMPDIFF(HOUR, yourfield, NOW() ) > 24

Or you could use
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

DELETE FROM tablename WHERE DATE_SUB( NOW(), INTERVAL 24 HOUR ) > yourfield

Or turn that around and do
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

DELETE FROM tablename WHERE DATE_ADD( yourfield, INTERVAL 24 HOUR ) < NOW()


And there are still more ways to do it if you just look at all the functions available in that same chapter.

Thank Old Pedant! I am still relatively new to MySQL - I appreciate the help!!!

Actually I have 1 more question.

I like the TIMESTAMPDIFF function, but when you say use 'yourfield' I typed in the column name of my database (called timestamp) but it didn't work.

Again, the column is called timestamp - how would I modify this query which I assume will delete anything that is over 30 minutes old?


$findold = "DELETE FROM deals WHERE TIMESTAMPDIFF(MINUTE, timestamp, NOW() ) > 30";
$execute = mysql_query($findold);

TIMESTAMP is a keyword in MySQL, so you made a minor error when you named your field that.

Not biggy. Just wrap any name that causes problems in back tick marks: `timestamp`

The back tick is usually on the same key as the tilde ~

You can always wrap a table or field name in backticks; will never hurt, may help.

TIMESTAMP is a keyword in MySQL, so you made a minor error when you named your field that.

Not biggy. Just wrap any name that causes problems in back tick marks: `timestamp`

The back tick is usually on the same key as the tilde ~

You can always wrap a table or field name in backticks; will never hurt, may help.

Great! So I will rename my timestamp column to "insertdate" and my query will become:


$findold = "DELETE FROM deals WHERE TIMESTAMPDIFF(MINUTE, `insertdate`, NOW() ) > 30";
$execute = mysql_query($findold);


I will let you know how it goes!
THANKS AGAIN :)

Ummm...you don't *NEED* to put the back ticks around insertdate since it's not a keyword.

But, again, they won't hurt.

Great insight - hopefully one day I will have this kind of knowledge readily available!!!

It's working great!

Thanks again Old Pedant!