How to do a recurring auto update correctly?

Question

I am creating a small browser game and recently I came up with a question about how to make a database value be manipulated (consulted/updated) every second, even with the user disconnected.

I have in my database a table called recursos with the columns ouro and ouroPorSegundo.

The way I was able to make it work is by creating a server-only page, in it using the setTimeout to, every second, call a PHP page that gives an update in the database.

Is this the right way to do it? I think it may be overloaded when using this method several times (I also use it to check if the troops/units have already returned to the "base" after an attack), and I need to keep an open page on the server to update the data of all users.

Edit: The game should work in the style of tribal wars, ikariam, they use some system that every second the database is changed, because you can receive an attack from another player, and depending on the second that receives the attack the stolen gold has a different value.

I’m doing it this way, on the server page:

$(function() {       
    getStatus();
});

function getStatus() {       
    $('div#status').load('getstatus.php');
    setTimeout("getStatus()",1000);      
}

getstatus.php:

$select = mysqli_query($conexao, "SELECT * FROM player_resources WHERE id = 1");
    if (mysqli_num_rows($select) >= 1) {
        while($row = mysqli_fetch_array($select)) {
            $ouro_hora = $row['ouro_hora'];
        }
        $ouro_seg = $ouro_hora / 60 / 60;
    }

    $update = mysqli_query($conexao, "UPDATE player_resources SET ouro = ouro + $ouro_seg WHERE id = 1");

Like I said, it’s functional, but the question is whether it’s the right way to do it.

Answer 1

I understand what you want to do, but this is not how you do it. Try to imagine a MMORPG, imagine the amount of hits that your database will suffer if it has thousands or millions of users.

For this case, the simplest thing to do is, each time there is a MANUAL transaction in the characters' resources, you record the current balance and the time it happened. Ex:

10/08/2016 11:52:30 100G

Assuming in your game the character wins 1G every 1 second, if I go to check my 12:00 balance, the query will return 550G, but in the bank will continue to recorded the same record above - 100G às 11:52:30. If 12 o'clock I spend 130G buying something, for example, then a MANUAL transaction will occur, and only update the current balance in the bank.

10/08/2016 12:00:00 320G

And again, when I go to check the balance at 12:01:40 for example, I will see that I have 420G. 'Cause just do the math before you show off.

This way your game will only make database updates when there is MANUAL resource transaction, not every second/player - which would make your game unviable. And also will only consult the bank and do the calculations when a player is playing, so your bench rests.

The way you were thinking of doing me, if I create a character and then never play again, your script will continue running for my character, every day, every second, forever.

UPDATE

In case the player is attacked, to know the amount of resources he has, just check the status of the balance and calculate the current balance. For example, if the balance status is 10/08/2016 12:00:00 320G, the time of the attack is 10/08/2016 18:15:40, then the current balance is 21.960G - considering the gain of 1G per second.

Answer 2

From what I understand you have a script running to update information in the bank even when the user is not logged in, right? If that’s right, you can make a simple PHP script that just changes the database information, remove the timeout and put the script to be executed on CRON of your server. The only problem there is that the minimum time of the CRON is 1 minute.