How to set up a MySQL row to be deleted 3 days after it's created?

Akito

It's fairly simple, but I just don't know how to do it. I can create a cron job to do the work, but I can't figure out how to setup the date right.

Here's what happens:

1) User goes through e-mail confirmation process. Their address, random string, and datestamp is stored in a database row. If they validate their e-mail address, then it's removed from this table and all is good.

2) If they don't validate it in 3 days, then it's removed from the database to clean up clutter.

However, I don't know how to make it delete itself after 3 days. Create a datestamp and see if it's been 72 hours since?

devinemke

DELETE FROM table WHERE date < DATE_SUB(CURDATE(), INTERVAL 3 DAY)

Akito

Forgive my ignorance, but could you explain what exactly is going on in that query?

Obviously it's looking in table for a date - what is the format of the date field? What's the date_sub doing?

devinemke

delete all records from the table called "table" whose field called "date" has a date that is older then the current date minus 3 days.

replace with your specific tablename/fieldname

Weedpacket

The format of the date field would be one of MySQL's date types (described in the MySQL manual). And of course, if you want to know exactly what DATE_SUB does, it's described in the MySQL manual as well. Funny, that.