Increasing resources overtime for a browser-based game [duplicate]

Question

I've been interested in developing a browser game ala OGame or Ikariam etc. Basically, a menu is displayed with your resources and, overtime, they increase depending on how advanced are you in the game.

Now then, the "resources" information for all players is stored in a MySQL database.

I've considered two methods to keep track of a player's resources:

---

Update database every second

Every second, something like

UPDATE `Players` SET `Gold` = `Gold` + `GoldProduction`

This is one option. However, performing a query every second for a potentially large table of players is not exactly appealing - but I might be underestimating my server. Am I?

---

Calculate it when needed

Since you get gold every second, technically the amount of gold any player has should be

(currentTime - creationTime) * goldProduction

What if the player spends gold? Well, gold spending should affect a variable goldModifications:

goldModifications + ((currentTime - creationTime) * goldProduction)

So if you spend 100 gold, goldModifications would be -100.

But wait. Players can upgrade their gold production. Hence, goldProduction isn't constant and this formula is flawed...

... Maybe when you upgrade your gold production, goldModifications will be modified by your current amount of gold (so if you had 1500, we would do goldModifications += 1500), then creationTime will be overwritten to the current time.

---

So I have two methods. One is incredibly simple (just update the database), but I am unsure if it is too much overhead or not. The other is very complex as there could be any number of factors affecting gold production, but certainly requires less processing power.

What should I do? What do those popular games actually do?

Answer 1

tl;dr

The resources should only be controlled by the Server (securely) as a decision making entity; The actual work could be offloaded to Clients and later verified lazily by the Server.

Long story:

How often you update the resources is up to you, your hardware and desired gameplay experience. I don't see why it requires you to update every second? What about every 10 seconds? But if it does, I am sure it is entirely possible with the right hardware. Remember, data that changes frequently could be stored in game memory and only updated to database for storage / backup purposes (with constraint validation).

When a user logs in (assuming production continues when offline), one could simply check the last date the user was online, how much X resource she had and use that to calculate her current amount of resource X. When she plays, let her handle the money, again checking if she is cheating by verifying constraints online. When the client makes a transaction, you could check that the current amount of resource X corresponds with the constraints (what the clients claims matches what the server thinks), calculating the resource state and leaving a tiny buffer for synchronization.

It all depends on the complexity of the game. In general I think you are doing three things that don't necessarily mix well (if taken to extreme) but could work fine depending on your server HW.

1. Updating game state frequently.
2. Stored all data on a server DB.
3. Run game as HTTP POST/GET requests, not a concurrent game server.

Again, I don't see why updating every second should be a huge concern but some benchmarks and tests you could easily perform would alleviate or affirm your suspicions. Then you could lower the rate of updates and/or employ a different strategy (offloading some of the work to client-side [Angular]) or something like that.

Answer 2

I am also trying to make a online game in PHP and encounter also this problem. My solution was actualy pretty simple. In Assuming you are using different city's or planets, you will need a database table for the city/planet. In this database you store the current resources and the resources per hour. And also a timestamp.

For upgrading resources facility (gold mine for example) i have a database that has all the building ques with a begin time and end time.

i then have made a page that i include on every page in the game. It first checks if there are any building ques for the selected city that should be finished since last time database was entered. If that gold mine was finished at let say 12.00 and the timestamp on your city was 11.00 and current time is 13.00. What the page does, it calculates all the resources for the time 11.00 to 12.00. Then it will update the gold per hour in the city table. And then it recalculates the resources from 12.00 to 13.00.

when all the above is done, it puts the timestamp on current. But as you have all the resources per hour from the second run, you can store it in an array and let a local timer run to update the resources on screen. But since you include the above mentioned page on every page, when you go to the next page it will acces the database to see what the current resources should be. So the local data is only for viewing.

I hope all is clear. Else just ask