How to see if a user is online in a website with php and mysql driven databases?

Question

I'm building a community website. Users will login and logout as usually.

I use the attribute status online/offline to set the user's status. But what if a user just clicks the X button or disconnects otherwise without logging out?

Lately my computer crashed, when I opened the site with my laptop I could not login because I don't allow login in two places. I go to PHPMyAdmin and I see my status still online. Is there any fix for this.

I tried the last_time activitiy thing but that doesn't work in case of a computer crash! And there was nothing neither interactivity or refresh to update the table.

Answer 1

You don't need the online/offline flag, you just need to save the last activitity time. When displaying the user status, if last activity time is less than now+15 minutes then user is online, offline otherwise.

Answer 2

CREATE TABLE `user_online` (
`session` char(100) NOT NULL default '',
`time` int(11) NOT NULL default '0'
) TYPE=MyISAM;

<?php

session_start();
$session=session_id();
$time=time();
$time_check=$time-600; //SET TIME 10 Minute

$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="user_online"; // Table name

// Connect to server and select databse
mysql_connect("$host", "$username", "$password")or die("cannot connect to server");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name WHERE session='$session'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

if($count=="0"){

$sql1="INSERT INTO $tbl_name(session, time)VALUES('$session', '$time')";
$result1=mysql_query($sql1);
}

else {
"$sql2=UPDATE $tbl_name SET time='$time' WHERE session = '$session'";
$result2=mysql_query($sql2);
}

$sql3="SELECT * FROM $tbl_name";
$result3=mysql_query($sql3);

$count_user_online=mysql_num_rows($result3);

echo "User online : $count_user_online ";

// if over 10 minute, delete session
$sql4="DELETE FROM $tbl_name WHERE time<$time_check";
$result4=mysql_query($sql4);

// Open multiple browser page for result


// Close connection

mysql_close();
?>

Answer 3

Because of the nature of the web, you can't know when a user disconnects, yanks the cable or shuts down his computer without politely telling you.

You could have a script (AJAX) check every X minutes to see if the browser still responds, and if not, toggle offline - but that would consume extra resources. This is how for example an IRCd works: they PING you, you PONG back. If you don't pong back, you timeout and get disconnected from the server.

HTTP is stateless, there is no other built-in solution. Maybe HTML5 and sockets, but that would be the same principle as just plain AJAX.

Answer 4

You can use this method 1. check user status if it is online check last activity if it is more than 10 min set status to offline if it is less than 10 min show till user online if it is off line show user offline

2. When user using this site update last activity in every "x" min with ajax
3. When user click on logout set status to off line. This could be your database structure.

Answer 5

You can run a function on each page request that updates a row in your database with your user's ID and a calculated timestamp for the future (e.g. time()+(60*5); - five minutes). Then whenever another user attempts to check if the first user is online, you can check it against the database using a 'pulse' check:

$time = time();
$query = mysql_query("SELECT user_id, timestamp FROM online_users WHERE user_id = '$user_id' AND timestamp > '$time'");

If this query returns more than 0 rows, the user is considered online.

Answer 6

Farfetched solution.

Use a node.js server with socket.io. Have the client connect to the server via the socket.io client side. The server is responsible for emitting events to the clients and expecting a response. On disconnect or late response mark the user offline.

It will work and probably will be working even on cable disconnects/browser closing but is it worth the effort?

Answer 7

wait man, i think i have the right reply because i did faced the same problem ! try to use the mouse event [click,movemouse ..] but with a delay to not creat a crash in the navigator, you will use setInterval on a xx sec or minutes after you save the event reply to your db in the activity column but with unix time in order to differentiate it later to the now time .. its useful really, i used it to log members acitvity to know what they really do in real time and get the online/offline users too. here is the used js functions:

var body = document.getElementById('body');
var url = './member-activity.php?loggedUser=<?=$memb_id?>&curl=<?=($_SERVER['QUERY_STRING'])?>';
if(document.addEventListener) {
  document.addEventListener("click", _EventLogging, false);
    document.addEventListener("mousemove", _EventLogging, false);
}else{
  document.attachEvent("onclick", _EventLogging);
}
function _EventLogging(){
//call it after a delay of 
setTimeout(AjaxUrl(url), 2000); //delay of 2 sec after every move of cursor
}
//AjaxUrl is a js function that calls a php file to perform the activity logging via AJAX
</script>
<div id="id"></div>

the div above is to notify you about errors in code, u remove it when its ok! i hope that my answer was right for ur case //dr.alpha@hotmail.co.uk

Answer 8

Well the real answer for ur question "How will i update the table without user interaction ?"

is MySql event scheduler , you schedule a an event (query) to be executed at whenever you wish even if the user is OFFLINE , read about it here (http://dev.mysql.com/doc/refman/5.1/en/events-overview.html)

Answer 9

actually the only accurate way to do that is using websocket, it will communicate with the user with real-time communication, try Ratchet.

Answer 10

You're on the right path: save user's last activity's UNIX time in the database, and, when someone accesses the site, set the offline status for users that were not active for 15 minutes (or less). If the user is logged in and his status is set to offline in the database, force him to logout (destroying the session).

However, the real question is: is it worth it? I haven't seen any similar authentication system yet.

Answer 11

I would use a combination of timing out sessions that have not had recent activity and expiring old sessions when a successful log in attempt is made.

As Konerak mentioned HTTP is stateless and there is no built-in way of telling if someone has left your site. You could figure it out with some sort of JavaScript based polling technique, but this would cause a lot of overhead that just isn't necessary in most situations.

Instead you should keep track of the last time there was any activity from a user and store that time in your database or better yet something like memcache. If a users last activity time is longer than a timeout period you decide on assume they are no longer on the site.

The second part would be instead of denying a log in when someone tries to log in after leaving the site without logging out, let them log in and invalidate any old sessions associated with their account.

Answer 12

As per your scenario, you can use the below logic to check the status of the user:

Instead of checking whether the user is online or offline only depednding upon the value of the table column, use the session variable as well. for example consider below code:

 <?php

  $stauts_from_table_column;

  if($stauts_from_table_column==1 && isset($_SESSION['userid']))
  {
   $user_logged_in = true;
  }
  else
  {
   $user_logged_in = false;
  }     

?>

You can enhance it further, but you get my point i hope.