I have reservation table(MySQL) and the schema is (id, status, checkin_date, checkout_date)

What I want is automatically update status when today is the checkin_date

What will be the best way to do this?

CodePudding user response：

I think, this is your answer: https://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

CodePudding user response：

you can use mysql events you can define what task you want to perform, provide time string at which you want to perform that task as condition event schedular will evaluate this condition and will perform task if condition is met. to create time specific conditions (such as everyday, every midnight, everyday at x hrs) you can use functions provided by mysql such as NOW(), TIME(), etc ...

something similar to what you want to do: https://dba.stackexchange.com/questions/64208/scheduling-an-event-every-day-at-a-given-time

mysql documentation for more insights on using mysql events : https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html

an alternative is to use webserver to perform mysql query at particular time :

In one project I used node.js as backend; used node module cron to perform mysql stored procedure every midnight.

some insights on mysql events vs cronjob Cronjob or MySQL event?

CodePudding user response：

to do a specific time in a specified time, CRON jobs helps in this most cpanels supports cron jobs