CODEWITHSUNDEEP
phpmysqlhtmlphpmyadmin
Adam Coulson
Adam Coulson

Reputation: 43

Deleteing record from MySQL Database after 1 Week

I want to know how to delete records from a MySQL Database after 1 week

This is the code I have for inserting the record and date of when it was added

INSERT INTO moviesdone VALUES ('" . $id2 . "', NOW())

Where $id is the name of what I am insterting and wanting to delete after 1 week.

I would also like to do this automatically, which I can set up from in phpMyAdmin

So, I only need to know: How do I write the date correctly (if needed) and how do I delete the record one week later?

Upvotes: 1

Views: 5399

Answers (2)

nico
nico

Reputation: 51640

If you are using MySQL 5.1.6 or greater you can use CREATE EVENT

Something like

CREATE EVENT del_entries
ON SCHEDULE EVERY 1 DAY
DO
   DELETE FROM table WHERE DATEDIFF(NOW(), creation_date) >= 7;

Upvotes: 5

Mark Byers
Mark Byers

Reputation: 837986

Run this code at regular intervals, for example once per day:

DELETE FROM moviesdone 
WHERE datetimecol < NOW() - INTERVAL 7 DAY

You can use the event scheduler.

Upvotes: 7

Related Questions