CODEWITHSUNDEEP
phpjavascriptmysql
alli123
alli123

Reputation: 33

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 :)

Upvotes: 2

Views: 1688

Answers (8)

Ioannis Karadimas
Ioannis Karadimas

Reputation: 7906

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.

Upvotes: 4

JuLy
JuLy

Reputation: 493

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

Upvotes: 1

alli123
alli123

Reputation: 33

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.

Upvotes: 0

Elzo Valugi
Elzo Valugi

Reputation: 27876

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>);

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92772

  • 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().

Upvotes: 1

Neil Aitken
Neil Aitken

Reputation: 7854

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.

Upvotes: 2

Svish
Svish

Reputation: 158181

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 :)

Upvotes: 4

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799110

Use a cron job to purge old messages.

Upvotes: 1

Related Questions