Automatic update of MYSQL table according to date

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

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.

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";
}

Use this,

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

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

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.

Every answer accepts your reason for the UPDATE. However, let me question that decision with extension to Col. Srapnel's suggestion from comments.

Updating can be seen as a waste of resources since you can calculate it on the run with in memory date comparison (which is orders of magnitude faster then I/O to storage).

There are two approaches to this:

• query/view can do this (and the cost should really be neglible)
• actually doing this on php side after results are retrieved (consider it a formatting/presentation task)
• it could even be done on the client side, which if possible, performance wise would be the best solutions (this might lead to maintenance/security issues)

Of course, there could be valid reasons for ignoring these solutions, but I think the performance by itself is not enough (most DBs are I/O bound).