How to Periodically Perform Operation on Database?

[dealmaker]

Hi,
In my website, I set certain rows to be deleted in a table, but I give user 30 days grace period to undelete them. After that, I will delete them. I want to know how I can do it? I use linux php apache mysql. Do I write a bash script that run a php file every day to check whether a record has been deleted for 30 days? What better way to do it?

Many thanks.

[Velox Letum]

Well, on my site, such as for sessions, I run a query which deletes any rows over five minutes old every load of the page, or on the user activation page it deletes any rows over 48 hours old before checking the activation hashes; you could do something similar. You can also use cron to run a php script from the command line, but unless you need it you should probably work around it. I use cron to run a backup script, that's all.

[dealmaker]

Do you use unbuffered_query for this purpose? I read it somewhere and it said that it doesn't wait til it finishes before returning from the function. It's faster in user's point of view.

What's the point of deleting rows over 5 minutes old on every load of page? Isn't it slower for all users because you are probably running a lot of unnecessary queries? And why not delete right away instead of waiting 5 minutes?

Well, on my site, such as for sessions, I run a query which deletes any rows over five minutes old every load of the page, or on the user activation page it deletes any rows over 48 hours old before checking the activation hashes; you could do something similar. You can also use cron to run a php script from the command line, but unless you need it you should probably work around it. I use cron to run a backup script, that's all.

[Spookster]

Your best bet is to use a cron job. You can either have the cron execute a PHP script or just write a shell script to connect to MySQL and execute your query.