I have a system whereby I wish to know who is logged in at any given time.

 

The way I believe I can do this is by sending an AJAX request every second or two seconds to the server from anyone connected. A database entry in some kind of "ActiveUsers" table is updated with the new time against the username, or an entry is created if the user is currently not in the table.

 

However, the problem with this method would be that I do not know how to implement a timeout to indicate that a user is logged out (their entry removed from the "ActiveUsers" table). The only way I can currently think of doing this would be to call a stored procedure (via a trigger) every time the "ActiveUsers" table is modified. This procedure would look at all the times for the entries in the "ActiveUsers" table and then remove any entries where the time is over, for example, 5 seconds from the current time.

 

Can anyone suggest an alternative that might be easier or more efficient or both?

 

Thanks in advance for your response.

 

I'm using PHP and MySQL.

You store the last time a user was "seen" as being online... surely they'll be offline when you haven't received an update for X amount of time (e.g. one minute)? You don't even need a separate ActiveUsers table - just update your regular Users table with the "last seen" time, and the ones whose (NOW() - ("last seen" timestamp)) < 60 seconds are offline, for example.

 

Whether this is efficient depends on how often you actually need to calculate the number of "active" users and how much CPU you have to spare. I doubt a "WHERE TIMESTAMPDIFF(MINUTE,NOW(),last_seen)<60" will add much performance overhead for a moderate number of rows.

 

Otherwise you can run a cron script or similar to remove users from ActiveUsers when their "last seen" time is over a minute ago etc. Or update the script where you fetch the ActiveUsers data to first purge the ones who haven't been "seen" for a while.

Edited March 1, 201115 yr by Connetu_C