Unity with MySQL and PHP => change Userstatus "online" after closing game to "offline"?

Scripting
1

Hey Guys,

I’m working on a simple testproject at the moment.
I wrote a Script so players are able to create and login to their account.
When the player logs in, the database-entry “isonline” changes from 0 to 1 (int), which works already.
But when the Game crashes, the internet connection brokes or something else unexpected happens, the entry remains “1”.
If the player uses the Logout-Button, it changes the entry through the script.

How can I make it possible that the “isonline” changes automatically if the player isn’t conncected for 30 or more seconds?

This is the Script I use (PHP)

    $sql = "SELECT * FROM users WHERE username = '".$username."' ";
    $result = mysqli_query($conn, $sql);
   
    //Get the Result and confirm Login
    if(mysqli_num_rows($result) > 0){
        //Show Data for each Row
    while($row = mysqli_fetch_assoc($result)){
            if($row['password'] == $password){
                if($row['isonline'] == 0){
                    echo "Login success";
                    $setonlinesql = "UPDATE users SET isonline ='1' WHERE username = '".$username."' ";
                    $resultsetonline = mysqli_query($conn, $setonlinesql);
                } else {
                    echo "You are already online!";
                }
            } else {
                echo "Password Incorrect";
            }
        }
    } else {
        echo "User not found";
    }

I’m not so good with PHP and MySQL but I will learn.
I know it’s not the best method but… do you have a better Idea how I can do that?

2

There are numerous ways, but which doesn’t require polling of any kind is to store the LastActivity DATETIME instead of storing online/offline as a flag.

LastActivity date can be updated directly with the current time from your procedures or queries (or you can use TRIGGERs on your tables to do this).

Finally you can add a VIRTUAL COLUMN or VIEW which shows online status by checking current time against lastActivity.

(Caps are keywords which you can google)

PS For your test project you could do the datetime comparison on the client side, just keep in mind this will fail if server and client clocks are out of sync so its not a good idea for something beyond a test.

1 Like