I wasn't sure where to post this so hopefully here is good.

Working on a browser game with a friend powered by a php backend. It's fairly simple but I can't decide when to update certain parts of the server.

For example the player can request to build something which, if allowed, puts an entry into their build queue and when its complete the entry gets moved out the queue and into a finished buildings table in the db.

Moving things out of the queue table and into the finished table is one such task - I could scan the table for a specific players jobs every time that player requests an update of their building status OR I could have a Tick() method or something that scans the entire table for all players at once and moves completed buildings into the finished table.

In the first case stuff is only done exactly when it's needed and if no players are online then nothing is done at all- however if 200 players are online all staring at their city then 200 db calls are being done every 2sec for updates.

In the 2nd case I'm always going to be scanning every 2 secs the entire table - much bigger dataset.. but alot of the work can be done with one single big db query rather than multiple small ones.

I have no idea which is more efficient ?

Obviously there are LOTS of task to perform (building, research, unit production, unit movements) that all basically have the same query.

Thanks for your time and advice!

If I were to do something like that, as first try I would send the finish time for each job in the queue when a player connects, and then let the client calculate any updates it needs to do without having to query the server anymore for that.

About the database, I would probably start by having a single table (instead of a in queue and finished table) and storing the end date of the jobs. Then I would use that to evaluate whether the job is done or not instead of moving things from one table to another. As long as you store enough data, you can reconstruct the current game state and only update the database on user input. I've never liked that much working with databases, though, so that might be a bad idea.

OR I could have a Tick() method or something that scans the entire table for all players at once and moves completed buildings into the finished table.

I don't see how you're going to implement a traditional Tick method with PHP given that nothing is running if nobody requests a page. But if you're talking about running it when someone does request a page, and performing all necessary catch-up tasks at that point, sure, it could work.

In the first case stuff is only done exactly when it's needed and if no players are online then nothing is done at all- however if 200 players are online all staring at their city then 200 db calls are being done every 2sec for updates.

100 db reads in a second is absolutely nothing. 100 db writes could be slow, depending on the data.

In the 2nd case I'm always going to be scanning every 2 secs the entire table - much bigger dataset

If the database is indexed on some sort of 'ready' time value then no, it won't scan the entire table, because that's not how databases work.

I have no idea which is more efficient ?

Most likely it does not matter at all.

Some hosting solutions, especially if you use some form of virtual machine type hosting, will let you run periodical tasks. You may not need it for this, but it might make your life a lot easier if stuff is supposed to happen even when noone is online.

Thank you for your responses :)

Avalander: that actually makes a lot of sense. One of the reasons I store just a start time was incase I added anything (server wide bonuses, etc) that sped up building etc which might change what that completion date was - but with some proper thought I'm sure that can be worked with.. for example, speed up bonuses could be deducted from the stored finished time or something.

The client will certainly do a lot of local calculations to keep the simulation smooth and to appear as if it was running right there and then as a local app - I'm just thinking that these days, esp with built in debugging consoles etc, it's not too difficult to mess with running javascript so my idea was to keep the client syncing with the server authority. But again after a moments thought I suppose the client just has to sync when something starts and when it THINKS something has finished to confirm?

Kylotan: The tick() would operate in much the same way as the other method - the server would store when it last ticked and if enough time has passed it updates everything and accelerates to where the world now is - tick() would be called infront of any function that gets data but would immediately return if it was too soon (ie lots of people playing). It wouldnt run if no one was online. The only benefit is to allow it to work with bulk data at once but from what I'm reading here maybe that is not such a benefit after all!

I know there are lots of games built in this way, Illyriad springs to mind straight away - but surprisingly little resources online about the design of engines like this! :)

These days I expect it's rare for people to attempt to do this with PHP, so a proper Tick() method can be used and there's little risk of having to run hours of updates in one go. But you can certainly handle things that way and as mentioned above, a cron job (or similar) can help you keep polling the server even when no player is present.

I think that you should process the queue in one go - i.e. all players at once, whoever the current client is. Data needed to be pushed to clients as a result of that can go into another table. And each client 'collects' that output with their periodic requests. (Unless you have a specific push system in place, in which case ignore that.) If your database is indexed correctly this should all be very efficient. For example, a Postgresql b-tree index can very efficiently handle sorted data - ideal for your build/event queue, where you often want to get the results with an end time before a certain point (e.g. 'now', to determine events that just completed).

For building games, storing the "finish time" (or perhaps the "start time" and "build duration") is absolutely the right thing to do.
This also lets the client do all the animation and estimation of when the building is complete; once the progress bar on the client is complete, the client simply asks the server what the state of the building is, and updates it.
That way, you only need to query buildings by their object ID, not by time. If you need the full state of the world, query all objects that belong to the player in the game instance, and derive their done-ness at the current date.
The client might want to periodically ask for a dump of all state anyway, to make sure it stays in sync, but this can be pretty infrequent, depending on the pace of gameplay. Every 1, 2, 4, 8, 16, and 32 seconds after a command has been given seems alright, with the presumption that once a command has been accepted by the server, the chance of it somehow reverting is slim.

I don't see how you're going to implement a traditional Tick method with PHP

I've seen PHP code that uses a process that hits a URL on a schedule to implement something like that.
I can't say that I like the pattern, because it wastes hardware resources a lot, but it can be made to work.
The benefit of PHP is that it scales horizontally very well, as long as you keep state in databases and network attached memory.
The drawbacks of PHP are that it's a home-grown hodge-podge of a language, which is a real problem for large projects, and its hardware resource requirements (constant factor) are very large.

Avalander: that actually makes a lot of sense. One of the reasons I store just a start time was incase I added anything (server wide bonuses, etc) that sped up building etc which might change what that completion date was - but with some proper thought I'm sure that can be worked with.. for example, speed up bonuses could be deducted from the stored finished time or something.

The client will certainly do a lot of local calculations to keep the simulation smooth and to appear as if it was running right there and then as a local app - I'm just thinking that these days, esp with built in debugging consoles etc, it's not too difficult to mess with running javascript so my idea was to keep the client syncing with the server authority. But again after a moments thought I suppose the client just has to sync when something starts and when it THINKS something has finished to confirm?

Yes, my statement about the client not having to query the server anymore was too bold. In the real world, as hplus said, you want to sync periodically with the server. What I meant is that you can avoid the client doing requests to trigger a state update in the database.

If the bonuses that accelerate the building speed are triggered by the user, you can send all the changed times back when the client makes that request. Otherwise, periodic syncs should be good enough. Or you can use websockets, so you can push the new state to the clients when something changes in the server without requiring the client to do a request first so the server can respond.