Automatic update of MYSQL table according to date

Question

I have a MYSQL table that holds events information. I need a mechanism that updates the status of each event according to its date ("over" if its a past date). What´s the best way to do such a thing? Update the status of the event when the any user logs in (useless routine for most users), create some sort of internal task (like a cron job?), or any other way. Right now, the status of the event is updated only when the creator logs in. This works but another user will see event as "scheduled" until the creator logs in, even if the date is past. Im using PHP by the way. Thanks

Answer 1

I would recommend updating the status any time the status is requested. Or better yet, don't even store the status in the database at all, but just compute it each time it's requested based on the other variables. So that way whenever you have a table list the status or someone request the status, just take the event date, compare it to today's date, and send them "not started", "ongoing", or "over".

Unless of course you need more possible statuses ("planning", "preparing", "setting up", etc). Then you would either need scheduled dates/times for each of these statuses or you would need to store a status variable. Either way, you could update the status (according to today's date and any other pertinent information) at the time that it's requested.

Answer 2

Updating at user logging in is not very good idea as it adds too much pressure (if there are a lot of users). The best way to do this would be to run a cronjob every night after 12 am/ or at the beginning of the day. If you still want to keep your old code, then wrap it with a date code. pseudo will look like:

$updated_last = last modification time of the file "updated";
if($updated_last is more than 1 day) {
 update db; (your old code)
 touch file "updated";
}

Answer 3

Use this,

UPDATE events SET status = 'over' WHERE DATE(event_date) > NOW();

Run it every day, once a day, with cron.

Answer 4

I'd go with a midnight cron job unless you have to deal with lots of timezones. Recomputing seems rather wasteful and computing on demand could get rather messy from a maintenance point of view as it leaves the data base in a partially incorrect state.