5

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!

Improve this question
6
  • Which database are you using? Commented Aug 11, 2015 at 9:12
  • 1
    Just use a cron task which will remove every data > 2 hours. Commented Aug 11, 2015 at 9:12
  • You can try with cron. I think that will be a best solution. Commented Aug 11, 2015 at 9:13
  • @bfontaine If you mean the software, im using PHPMyAdmin, otherwise its just MySQL I guess... Commented Aug 11, 2015 at 9:14
  • dev.mysql.com/doc/refman/5.1/en/events-overview.html - use mysql event scheduler Commented Aug 11, 2015 at 9:15

4 Answers 4

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Yeah, I probably can't use cron jobs, as my web host only allows once an hour or more... Only thing is, that seems pretty complicated..
Actually, not too complicated, but I have my table already made in PHPMyAdmin. So if I just add a current timestamp, can it just be set every time new data is added
Yes that is what will happen. You may need to clear out the rows that existed prior to the table change manually though
OK, thanks! Could you give me a run-down of the exact code I should put and where?(sorry, I'm a noob)
I assume you mean the PHP code to execute the DELETE. Well not without some idea of your existing PHP code I cant.
|
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.

Improve this answer

1 Comment

please do mark my answer as correct if this answer helped you.
0

Just add a column remove_time (DATETIME) and set the time you want the row to be deleted. Than use cron (configuration depends on webhosting you have) to run this query (probably as poart of PHP script):

DELETE FROM table WHERE remove_time <= NOW()

You can configure cron to run every minute (or less/more, depending on your needs).

Improve this answer

Comments

0

  1. Try implementing a cron which will run at specified time automatically to check and delete the rows whose created_at is less than the current_time by 2 hours.

  2. On how to implement cron, check Skilldrick's answer here

Thank you :)

Improve this answer

3 Comments

What do I put inside cron.php?
EDIT: I guess I put the delete part in there... Anyway, so I should add created_at and current_time to my table?
Add only created_at in your database. The current_time variable will be set in php --> $current_time = time() ; This will return you the Unix timestamp.The next step is to calculate the unix timestamp 2 hours before the current timestamp,say $temp_timestamp. And finally, delete the fields whose created_at is less than that the $temp_timestamp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.