Suppose there is a database which contains a table that has date and events column, there are total 10 records now I want that the records which are older then the current date should automatically be deleted and the present date records should be present in the database so as the future records should also be present, does anyone know something like this or can help me how to achieve this. any references would be helpful. I am open to php, phpmyadmin, sql. Any help would be appreciated.
1
-
5Possible duplicate of Does MySQL have time-based triggers?Psi– Psi2017-04-01 09:40:35 +00:00Commented Apr 1, 2017 at 9:40
Add a comment
|
2 Answers
First it's too broad. You can consider using MySQL Event Scheduler for this purpose. Which you can set to run on every 24 hours and delete all records less than NOW()
Activate event support :
SET GLOBAL event_scheduler = ON;
Add sheduler :
DELIMITER |
DROP EVENT IF EXISTS `clear_old_rows`|
CREATE EVENT `clear_old_rows`
ON SCHEDULE
EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
ENABLE
COMMENT 'clear all rows < CURRENT_TIMESTAMP'
DO
BEGIN
DELETE FROM tablename WHERE date < CURRENT_TIMESTAMP ;
END |
DELIMITER ;
