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.

Solution 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.

Solution 2

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.

Solution 3

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();
?>

Solution 4

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.

Solution 5

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?

View more solutions