Check if user is online or not .Too many ajax requests

Question

Take look at my users mysql table.

id|active|

These are what active values means.

0-Offline
1-Online
2-Idle

• Whenever someone logs in i update the active column and set it to 1.
• Whenever someone logs out i update the active column and set it to 0.

Though it can happen like someone without logging himself out closes the browser or maybe a power cut. Then the logout.php is not occured and it shows that user is still active.

I want to prevent this thing. If this thing happens i want the active table to update to 2. But update to 0 if user does not logs it again under 5 hours.

I also dont want too many ajax request per minute.

Answer 1

Cron Solution

• Make it so every-time the user visits a page/browses the site you update them to active = 1 and put in a time=time();

• Create a cron script to update all users who's time is lower than a specific amount, you can have 1 cron to run every 15 minutes, which can include both queries (as it will tick those at 5 hours when the time hits)

Queries in the cron file

UPDATE users SET active = 2 WHERE time < (time() - 900);
UPDATE users SET active = 0 WHERE time < (time() - 18000);

If you need some form of real time state, say the user visits a page but does not leave it (and does not close browser), then you can create a set interval to update them, say every 15 minutes.

The key is, giving yourself some kind of average estimate. Trying to detect who exactly is online at a per second basis in realtime is not good with ajax, it is not built for that purpose and you should be looking into something like NodeJS/Websockets. If however 5, 10 or 15 minute leeway is an acceptable "guess", then its very simple.

At query solution

Alternatively If you cannot use cron (shared hosting), then you could base it completely off time rather than active state. Thus when you query to show who is online, you would do an if condition within the SQL query to determine if their active state should be a 0, a 1 or a 2, have a read up on http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html for that

select 
    username,
    CASE 
        WHEN active_time > (UNIX_TIMESTAMP(now()) - 900) THEN 1
        WHEN active_time > (UNIX_TIMESTAMP(now()) - 18000) THEN 2
        ELSE 0
    END AS active_state
FROM users
ORDER BY active_time DESC

If you dont want to query offline, you could add WHERE active_time > (UNIX_TIMESTAMP(now()) - 18000), the reason not to where on active_state is because that needs to be calculated before where can be done, whilst active_time is a field and can be filtered first before the select calculation, cutting down MySQL resources.

Store your active time with a unix timestamp, either using UNIX_TIMESTAMP(now()) or PHP's time()

This will return user with their respective active states, this is on a select though but you could filter the results purely based on time and not rely on a active state being a specific number.

Answer 2

You can make an own session handler. --> session_set_save_handler
Store the sessions in a database like this: Link

Then you can make a call with SQL to the database in the _destroy function, that set the active column to 0.

That means when the session is ended (the browser is closed, or the session is expired) the session_set_save_handler calls the _destroy function

Answer 3

You don't need many ajax requests. You can execute one request per few minutes and save the time of the request in the users profile. You can run a CRON job for a script that checks each user with status 1 (online) and if the last request time is too long ago you can change the status of the user to whatever you want.