Auto-delete messages in MySQL database

Hi I have a MySQL database table where a user inputs messages (via a form)开发者_如何转开发 and I was wondering if there is a way to automatically delete a message after say 1 minute has passed? The code i'm using is PHP. Thanks very, very much for any replies :)

Well, you could always run a delete query before you do anything else. For example whenever you check for messages, first delete all messages older than 1 minute.

I think I would rather just not get the messages older than 1 minute though. It can be nice with a log :)

I can think of two ways to achieve that.

1. Make a script to run every X minutes and make any changes in your db.
2. Delete all expired records before or after inserting new records in your script.

When a message is inserted to the DB, store a time_created timestamp. Then in your PHP, you only display messages whose timestamp falls within 1 minute of the current time.

Use a cron job to purge old messages.

• You could make a cron script to run every minute and delete all old messages
• or make a valid_until column in your table, and set it to NOW()+60 - then only show rows which have valid_until >= NOW().

Building a cron service is the first thing that pops into my mind, although is probably an unnecessary complication.

You can call the delete in the same script that does the insert after a sleep of 1 minute.

 sleep ($seconds);
 // call the delete query

Another way is to pass the delay logic to a Mysql trigger that will do the delete for you.

SELECT SLEEP(<seconds>);

Solution 1: user cron job which runs every minute to check and delete

Solution 2: use ajax do that job

I personally suggest the first solution but if u have no SSH access to the server u have to choose the second one :D

thanks for all the replies/help :) the first suggestion by Svish 'You could always run a delete query before you do anything else. For example whenever you check for messages, first delete all messages older than 1 minute.' will work...so obvious don't know why i didn't think of that doh! Thanks again every1.