Timed events with php/MySQL

Question

I need a way to modify a value in a table after a certain amount of time has passed. My current method is as follow:

• insert end time for wait period in table
• when a user loads a page requesting the value to be changed, check to see if current >= end time
• if it is, change the value and remove the end time field, if it isn't, do nothing

This is going to be a major feature on the site, and so efficiency is the key; with that in mind, you can probably see the problem with how I'm doing it. That same chunk of code is going to be called every time someone access a page that needs the information.

Any suggestions for improvements or better methods would be greatly appreciated, preferably in php or perl.

In response to cron job answers: Thanks, and I'd like to do something like that if possible, but hosts limits are the problem. Since this is a major part of the app, it can't be limited.

Answer 1

Can you use a cron job to check each field in the database periodically and update that way?

A big part of this is how frequently the updates are required. A lot of shared hosts limit the frequency of cron checks, for example no more than every 15 minutes, which could affect the application.

Answer 2

why not use a cron to update this information behind the scenes? that way you offload the checks on each page hit, and can actually schedule the timing to meet your app's requirements.

Answer 3

Your solution sounds very logical, since you don't have access to cron. Another way could be storing the value in a file, and the next time the page is loaded check when it was last modified (filemtime("checkfile.txt")), and decide if it needs modifying again. You should test performance for both methods.

Answer 4

If performance really starts to be an issue, (which means a lot more than you probably realize) you could use memchached to store the info...

Answer 5

You could use a trigger of some sort on each page load. I really have no idea how that would affect performance but maybe somebody else can shed some light.