Add data to a database, automatically remove after a certain time?

Question

So, I've done quite a bit of googling on this topic, and I just can't find an answer. So, basically, I'm looking to make a small website, that will pull information from a HTML form, send it to a database, then after two hours, it will automatically delete itself. I have a basic theory on how this could work, but I can't figure out how to do it: I could pull the current time and date, add two hours to that, then put the time into an "expires" column in the database. Once the time is the one that is in the expires column, the data will be removed from the database. Sorry if this is a very "noobish" question, I'm still a bit new to databases with PHP.

Anyway, any help would be much appreciated! Thanks!

Answer 1

You could add a new timestamp column to your table which will automatically add the timestamp of when the row was created like so

CREATE TABLE t1 (
   #your existing columns defined as before + this new column
   ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now every time you create a row on this table, MySQL does all the work of recording when it was created.

Assuming you may not be able to create a cron job on your host you could then add the deletion code in the most obvious place in your existing site code to do the removal.

// remove old stale data
$sql = "DELETE FROM user
        WHERE ts_created < DATE_ADD(NOW(),INTERVAL -2 HOUR)";

if ( ! $mysqli->query($sql) ) {
    // log $mysqli->error somewhere
}

ALthough a cron job seems a good idea at first sight, in order to make sure things are always accurate on this table you would have to run it every 30 seconds or maybe even more often. That would get in the way of other activities on this table, if the site was busy that could be a problem, if it was not busy you would just be running the cron unnecessarily most of the time.

If you add the deletion code just before you present this information to the user at least it would only be run when required and you would also ensure that the table was always accurate at the time the data was presented to the user.

Answer 2

You can ensure the scheduler starts when MySQL is launched with the command-line option --event-scheduler=ON or setting event_scheduler=ON in your MySQL configuration file (my.cnf or my.ini on Windows). Run this query statement in mysql

SET GLOBAL event_scheduler = ON;

Create an mysql event scheduler using following - this will behave like Cron Job but actually it is a mysql trigger on specific interval. This is triggered from mysql server.

CREATE EVENT e_hourly
 ON SCHEDULE
  EVERY 1 HOUR
COMMENT 'Clears out sessions table each hour.'
DO
  DELETE FROM table_name WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(remove_time) > 120

http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://www.sitepoint.com/how-to-create-mysql-events/

Pardon my explaination - I myself have implemented this just now and it worked.