I need to design and implement a timer-based game (Flash on client, PHP on server), like Cafe World, i.e. user clicks on a button, waits a few seconds, something happens, and then he can click again. It'll be a simulation of a food production line.
There will be N production line elements, each has a separate timer with different duration, and each can cue up to M operations (first starts immediately, next starts when the previous has finished and so on).
How do I implement the server side of that kind of feature with a database backend? Currently I'm thinking about a counter for the cue, "time left" indication for currently active operation and the time of last update. When an operation is requested, I'd update the "time left" and cue counter using the time elapsed from last update. Any thoughts, comments or better ideas?
Best answer gets the bounty.
After reading through your question, other answers, and the discussion, I think I finally have a grasp on what you are asking (hence a new answer).
To begin, you need to track an end-user's session. When the game begins, you will have to start a new PHP session and send some sort of session identifier to the flash game. Whenever the flash game pings the server, it will pass along the session identifier so PHP knows which instance of the game it is working with.
The PHP server itself will be a passive entity -- the flash client will be doing the pushing and pulling of data. Let's say a user queues events X, Y, and Z, and they each take 20 seconds... As soon as the user attempts to queue each event, the flash client notifies the server, and the server either records it in the queue, or returns an error due to validation (ie, too many events in queue).
In addition, the client will poll the server every few seconds to check on the status of events. Every single time the client registers a new event, or polls for status, the server is going to check the timestamps in the queue and mark events as finished/etc, and then send a response to the client. This is much cleaner and much more practical than trying to run a script in the background that attempts to update the database every second in real time. The only downside is that events for a specific session aren't processed until a client reconnects if there has been a disconnect for whatever reason. But regardless of the amount of time in between connections, the user will still perceive the server as updating in real time because data is always refreshed just before it is sent back to the client.
If you have any implementation-specific questions, please feel free to post your database schema and/or your server code.
Update: In response to Pie's comment, there are essentially three ways to mark events as finished... 1.) Mark finished events as finished in the database when the client connects to the server for a request, or 2.) automatically mark events as finished by using a background process, or 3.) a combination of both.
If you follow option one and check your event timestamps and mark finished events upon every client connection, your database will be fully up-to-date when serving the client's request. If you follow option two, a PHP script running minutely on cron can insure that the database is up-to-date even if the client disappears, ensuring that your data is usually less than a minute fresh -- regardless of whether the client is present or not.
Regardless of how you are going to be using the data (high scores table, etc), having a combination of both techniques is probably the best idea. Fresh data is always a plus, and periodic incremental refreshes from a cron script never hurt anyone.
As far as marking events as completed is concerned, you merely need to check the relative start time of the event + the duration of the event against the current time. In a queue, the start time for an event is defined as the duration and cool-off times of all prior uncompleted queued events added to the starting timestamp of the first item in the queue.