CODEWITHSUNDEEP
phpmysqlsql
ePascoal
ePascoal

Reputation: 2442

How can i delete a row from time to time from my database

My idea is: if anyone write a message on my database i want to delete that message 24 hours after being inserted on my table 'messages'. Triggers just act after or before and insert or update, but i want to delete in realtime when this message makes one day of existence. Im doing this for an webapplication.Is this an PHP matter instead SQL? Can you show me some possible approach to do this?

Thanks in advance.

Upvotes: 0

Views: 161

Answers (2)

Captain Skyhawk
Captain Skyhawk

Reputation: 3500

You will have to run a scheduled job. Simply delete records where their insert/creation time is over 24 hours from the current time. If you don't currently have a "creation time" field, be sure to create one so you have something to compare against.

Have it run every half hour, hour, or however many times you'd like it updated.

You can find more about scheduling here:

MySql Event Scheduler

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Don't delete the record every 24 hours. Instead, calculate a flag, when you retrieve the data, that says whether the record is older than 24 hours. Just filter them out. You can set this up using a view.

Then, at your leisure, delete the messages older than 24 hours. You can do this once per day, once per week, or whenever you like. A large number of deletes can adversely affect performance, so you might want to wait until a quiet'ish time to do the deletions.

Upvotes: 4

Related Questions