This is a pretty common task that doesn’t have a perfect answer. It’s not possible to determine if a user disconnects, leaves the page, closes the browser, etc (besides window unload methods, which aren’t practical for this use case). So we fake it as best we can. Here’s how I do it.
1. Create a table with 2 fields: ‘user’ (whatever datatype your user PK is – probably INT) and ‘ts’ TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. *
2. On any page load, update the activity table – pass any value here as MySQL will handle the timestamp.
4. Whenever a Friends/Contacts list is open (which might be all the time on certain pages), poll the server every Y minutes o see who’s online (e.g., every 3 minutes – I try to have this interval at least 3 times larger than the update interval, to give the update multiple opportunities to qualify a login update). This might look like SELECT * FROM `users` JOIN `activity` on `users`.`id` = `activity`.`user` WHERE `activity`.`ts` > DATE_SUB(NOW(), INTERVAL 5 MINUTE)… You’d probably also want a WHERE to qualify friends of contacts of the current user.
* Note that this could be accomplished by just adding an ‘activity’ timestamp to the users table, but I prefer a dedicated table.