CODEWITHSUNDEEP
mysqlsql
webworm
webworm

Reputation: 11019

SQL query to determine which users are online

I am trying to get a list of players who are logged into an online game (DayZ). The backend database is MySQL and the way to determine if a user is logged in is not exectly straight forward. Is is building this query that I could use help with.

Basically there is a table named player_login that makes a new entry everytime a player logs into and out of the system. If the user logs in then a field named action is set to 2. If the user logs out the action field is set to 0. Here is some smaple data (simplified)

LoginID     PlayerUID   DateStamp               Action
126781      79067462    2013-08-01 13:16:28     0
126777      79067462    2013-08-01 12:59:22     2
126775      79067462    2013-08-01 12:42:10     0
126774      79067462    2013-08-01 12:41:34     2
126773      79067462    2013-08-01 12:38:38     0

I can query the table to find out if a single user is logged in via the following query ..

SELECT PlayerUID, Action
FROM player_login 
WHERE PlayerUID = 79067462 
ORDER BY Datestamp DESC 
LIMIT 1

If the result is 0 then the last thing the player did was login and is therefore online. If the value is 2 then the last thing the player did was logout and are therefor offline. What I am having some difficulty doing is transforming this query into something that would return a list of PlayerUIDs where the latest Action value is 0 and thereby giving me all players currently online.

Upvotes: 0

Views: 699

Answers (3)

Jeff
Jeff

Reputation: 29

I think you want to sort by distinct player Id's otherwise you will get all times the player has logged in or out:

SELECT DISTINCT PlayerUID, Action
FROM player_login
WHERE 'Action' = 0
ORDER BY Datestamp DESC

Upvotes: 0

Dan J
Dan J

Reputation: 16728

The following query yields the most recent logout time for each player:

SELECT PlayerUID, MAX(DateStamp) AS MostRecentLogout
FROM player_login
WHERE Action = 0
GROUP BY PlayerUID

A similar query with Action = 2 yields all most-recent login times. Combining these lets you compare them:

SELECT plin.PlayerUID, plin.MostRecentLogin, plout.MostRecentLogout, 
       CASE WHEN plout.MostRecentLogout IS NULL 
              OR plout.MostRecentLogout < plin.MostRecentLogin 
            THEN 1 
            ELSE 0 
       END AS IsPlayerLoggedIn
FROM (SELECT PlayerUID, MAX(DateStamp) AS MostRecentLogin
      FROM player_login
      WHERE Action = 2
      GROUP BY PlayerUID) plin
LEFT JOIN (SELECT PlayerUID, MAX(DateStamp) AS MostRecentLogout
           FROM player_login
           WHERE Action = 0
           GROUP BY PlayerUID) plout ON plout.PlayerUID = plin.PlayerUID

Note the LEFT JOIN and attendant NULL handling in the CASE statement: this is meant to handle players that have logged in for the first time and never yet logged out.

Upvotes: 3

echo_Me
echo_Me

Reputation: 37243

try this

   SELECT PlayerUID, Action
   FROM player_login 
   WHERE `Action` = 0
   GROUP BY PlayerUID
   ORDER BY Datestamp DESC

Upvotes: 0

Related Questions