Lberteh Lberteh - 3 months ago 17
MySQL Question

How to periodically update status in MySQL

I'm creating a todo app. I have a status column that receives

1, 2 or 3 (pending, overdue, completed)
.

Whenever I create a task it is set to
pending
. The user can mark it as complete. But is there a way to automatically update it to
overdue
in case it's not completed and
due_date
is less than today?

Answer

You can use MySQL event Scheduler.


Prerequisite:

You have to have event_scheduler ON in your mysql server.

Check whether event scheduler is ON or OFF

SELECT @@event_scheduler;

To turn event_scheduler ON run the following query:

SET GLOBAL event_scheduler = ON;


Note: If you restart MYSQL Server then event scheduler status will be reset unless the following is written in the configuration file.

For Windows: in my.ini file write this under [mysqld] section

[mysqld]
event_scheduler=on

For Linux: in my.cnf file

[mysqld]
event_scheduler=on

Event:

CREATE 
EVENT `updateStatusEvent`
ON SCHEDULE EVERY 1 DAY STARTS '2016-08-11 00:00:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
UPDATE your_table SET status_column = 2 WHERE your_time_column < CURDATE(); 

The event will be started for the first time at '2016-08-11 00:00:00' and after that the event will be scheduled in every 1 day interval and will update the status of the corresponding data.

Comments