Suppose I have a table called temp_data. I want to delete row 10 minutes after it’s creation. I know this can be done in php via corn(job/tab). But I would like it to be done in mysql itself if possible. I did find something similar (almost exactly same) solution here: http://bytes.com/topic/mysql/answers/655510-delete-rows-every-30-minutes
But I cant clearly understand how that works unless it’s in mysql/php mysql_query(QUERY); format.
currently my table has two fields: id and ip.
Basically I want an entry to expire after 10 minutes of it’s creation, so any suggestions from you experts?
If your table currently only has 2 fields, you’ll need to add a timestamp field (with default as CURRENT_TIMESTAMP) to be able to tell when the row was added.
Then you can run the following MySQL query…
DELETE FROM `table` WHERE `timestamp` > DATE_SUB(NOW(), INTERVAL 10 MINUTE);
You will need to run this as a cron though. AFAIK it can’t be done in MySQL alone.
If I have to use cron for this, then I would have to run it every one minute if it where to be accurate. Being in a shared hosting I’m afraid they’ll kick me out if I run this every minute. Any suggestions?
well, that’s certainly a worthy objective, but how is automatically deleting stuff after 10 minutes going to help you spot those things? wouldn’t that just make it harder?
also, what’s wrong with using multiple browsers or windows to browse?
Well suppose the user is credited real money to stay in the page for 5 or so minutes, in that situation cheaters will be tempted to open multiple browsers/windows. Well this is to prevent that. The ten minute delete is required because if the page got interrupted (via user accidentally closing the window, net problem) before the user is credited the money, they would get a chance to view page after 10 minutes. As it is if the user legally got credited the very first time they opended the page, it will be removed from temp_data accordingly.
Could you possibly use an ajax/javascript call to automatically update a row after a certain amount of time?
I.e. Javascript countown, after 10 minutes, calls php_file.php?id=123456, php_file.php updates row 123456 to show that the browser was active for the required time.
But I want to be able to clear the row even if the user shutdown their computer and went away. That’s why it has to be automatically preformed. Guess using cron is the only option.
I don’t want to run cronjobs etc to execute the deletion. I was hopeful that there would be somewhat row expire functionality in mysql on the row creation itself, like in cookies.
There isn’t. But as others have been saying, you do not need to use a cron job to delete the rows. You have the same knowledge available to you with the rows still there just by examining the timestamps on the rows.
Wait, I didn’t think of it deeply before. While comparing data on temp_data table, rather than checking if the entry exists I should check if entry with less than 10 min interval exists and clear the log every week or so. Great idea!
if you are unable to run cron, then you need to redesign your program.
what you need here is a state machine (FSM), you need to guide user to finish whole process, the process can not go back, only forward, and can only been done from start to end (from first step to last step), any one try to start from middle will immediate kick out.
also state machine will prevents multiple browsers/windows problem
