Mysql auto update event

Question

using php and MySql, is there anyway to get the date in the database to update by its self when a date has expired. i.e. event name x is on date 2012-05-12, on 2012-05-13 the date should change to 2012-05-19 (a week from 2012-05-12)

thanks guys

Answer 1

You can use MySQL's event scheduler:

CREATE EVENT update_date ON SCHEDULE EVERY 1 DAY STARTS CURDATE() DO
  UPDATE events_table
  SET    event_date = ADDDATE(event_date, INTERVAL 1 WEEK)
  WHERE  event_date < CURDATE();

In order to make the event scheduler run you can add this in my.cnf:

[mysqld]
...
event_scheduler=ON

and with superuser privileges, you can set the global variable on the fly:

SET GLOBAL event_scheduler='ON';