Auto-Delete with Cron Jobs

[gwynevere]

Right now, I'm using this code on my site:


$killtime = "(NOW() - 32000)";
mysql_query("DELETE FROM status WHERE time < $killtime") or die(mysql_error());

It works, but the problem is that it deletes EVERYTHING old AND new mysql entries for some reason. What I want is for it to delete old messages that are three days old and of course keep the newer ones. I read on google that it is probably easier to set up a cron job to do this, but I don't have any knowledge about cron jobs.

What would my php script be (one that works properly), and what command do I enter in the Cron Jobs in CPanel.

Thanks!

[pghcollectibles]

the cron would still need to execute a script.

as a test... insert an echo to verify $killtime, and i think your string should not have quotes. usually the comparison string is quoted in a query. try this:


$killtime = NOW() - 32000;
echo $killtime;
mysql_query("DELETE FROM status WHERE time < '{$killtime}'") or die(mysql_error());

if it works, just erase the echo.

also is NOW() a defined function? i didnt find it in php.net

[gwynevere]

Yeah, NOW() captures the current time. As for your code, I'm afraid it didn't work :( I test it with entries that were posted at different times and it just wiped out the entire table. Not sure what I'm doing wrong. My 'time' column is set up as a timestamp.

[r2b2]

Why not just use MySql's date functions? A query like

DELETE FROM status where time < DATE_SUB(NOW(), INTERVAL 3 DAY)

might do the trick (I'm just guessing from the reference guide). As an aside you may want to jump into phpMyAdmin and play around with doing SELECTs rather than DELETEs to get your syntax right prior to actually running the script...

Hope that helps!