0

Can database remove rows itself with condition?

Usually I have removed data from database by user action, but now I want database to do it by itself. It should be able to delete rows with timestamp without user. Every time when row has older timestamp than NOW() it deletes itself.

Can anyone recommend any databases or (tools) for my project?

Tools that I am going to use in this project is strictly PHP, Javascript, HTML and CSS.

I am familiar with MySQL, but I haven't found any information from the web that would answ er to my question. At least not to my cause.

Currently I have this set up

User adds "task" to MySQL "tasks" table with startdateand enddate (both are in mm/dd/yyyy format) alse active which is 1.

I need to create event that checks each day (at midnight) each row in "tasks" table is this day older than enddate and if it is update active into 0 otherwise leave it as it is.

Jaakko Uusitalo
  • 655
  • 1
  • 8
  • 21
  • 1
    Did you check this [What is the best way to delete old rows from MySQL on a rolling basis?](https://stackoverflow.com/questions/9472167/what-is-the-best-way-to-delete-old-rows-from-mysql-on-a-rolling-basis) – Sorix Dec 08 '17 at 11:54
  • 1
    Of course, see [How do I delete rows of data from mysql table automatically with 24 hours after data into table?](https://stackoverflow.com/questions/44253746/how-do-i-delete-rows-of-data-from-mysql-table-automatically-with-24-hours-after) post for example. – Alexander Dec 20 '17 at 10:34

1 Answers1

1

You can try using this condition:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)
So that the whole SQL script looks like this:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE

DO BEGIN
      DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;
vijayvicks
  • 161
  • 10