How to delete MySQL records after a certain time?

[Vince889]

How do you delete MySQL records after a designated time? Does it require a cron-job?

 

Allow me to explain the situation:

I am submitting a record to the database, included with a mysql timestamp and the number of hours that the the records should be kept.

So an example record is: "UserA |  2009-12-08 12:30:52 | 1"

Meaning, I want PHP to delete 'UserA' in one hour. Now mind you, there will be a lot of different records in my database with different deletion times.

 

I was assuming that I would have to do something extremely verbose and annoying like looping through each record, checking the timestamps and converting the 'hours' into timestamps somehow, then checking if the record is due to be deleted... Sounds really complicated! Is there another way to do it?

 

out of everything in php, i find date/time calculation to be the hardest

[Deoctor]

yes u need a cron for doing this..

[Psycho]

First off, make it easy on yourself. Instead of storing the hours before deletion, store the timestamp of deletion (if you absolutely need the time to deletion, then store that too). Then, you do not need to do any calculations when it comes time to delete, simply run a simple query such as:

 

DELETE FROM table
WHERE deleteTime < NOW()

 

As ym_chaitu stated above you could do this through a CRON job (google for more info). However, do you really need to delete the records? You could make the delete time an expire time. Then just use the expire time to exclude any expired records when doing the normal queries.