Search code examples
phpmysqlexecution

php/mysqli query not executing some of the query's with no errors

I have a script running every minute on my server to do basically a cron job to update some variables in the database for a small game i am making for some friends.

The issue i am having is that the script only runs some query's but fails to update others. i have verified that it is connecting but a comparison is not working. any help would be appreciated.

this is my current call that is not working.

//Query server
$query = "SELECT id, usr, dt, is_online FROM player_data WHERE is_online =1";
$result = mysqli_query($conn, $query);
// If connection is good
if ($result = mysqli_query($conn, $query)) 
    {
        echo "Connected </br>";
    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($result)) 
        {
            //Get time of NOW, and time of last activity
            echo "loop started </br>";
            $now = strtotime(date("Y-m-d h:i:s", strtotime("now")));
            $before = strtotime(date("Y-m-d h:i:s", strtotime($row['dt'])));
            //Add five minutes to the last activity
            $then = $before + (60 * 5);
            //Display the times
            echo $before . "</br>";
            echo $now . "</br>";
            echo $then . "</br>";
            //determine if the time now is more then 5 minutes after the last activity
            if (strtotime($now) > strtotime($then))
                {
                    //set user to offline if more then 5 minutes has passed.
                    mysqli_query($conn, "UPDATE player_data SET is_online = 0");
                    echo "1.User: " . $row['usr'] . "</br>";
                    echo "1.Database: " . $row['dt'] . "</br>";
                    echo "1.System: " . date('Y-m-d H:i:s') . "</br>";
                    echo "Now: " . $now . "</br>";
                    echo "Before: " . $before . "</br>";
                    echo "then: " . $then . "</br>";
                }

        }

    }
`````````````````````````````````````````````````````````````````````````
this should set anyone who has not been active in the last 5 minutes to is_online = 0 but does not.

This is the actual output as of right now with the last activity being more then 1.5 hours earlier.


Connected 
loop started 
1555687200
1555777824
1555687500
loop started 
1555687227
1555777824
1555687527
Solution

  • Well let me guess, the script is setting to everyone to offline, right? The thing is that you have missed the WHERE clause in our UPDATE statement

    mysqli_query($conn, "UPDATE player_data SET is_online = 0");

    So it is better to review that part. It is supposed to be like this

    mysqli_query($conn, "UPDATE player_data SET is_online = 0 WHERE id = " . $row['usr']);

    Also there is no need to compare strtotime($now) and strtotime($then): the $now and $then are already holding "microtime" values, so you can just do like this:

    if ($now > $then) {
    //your code here
}

    BTW, even if you are not using any REQUEST parameters, I'd suggest to use prepared statements.