Introduction
Knowing your website statistic is a very important factor in tracking the growth of your site. A more obvious use of such statistical information in is forums, communities, and social networks where it is of paramount necessity to know
- If a user is online (useful in Live Web chat)
- The number of users currently online
- The highest online ever (with date and time)
- The number of users that has been online for that day
- The time and date a user was Last Seen
This article presence how these can be achieved using PHP and MySQL. Implementing this using PHP and MySQL is pretty easy and straightforward (trust me on this 
). You can grab the source files attached to this article which contains two files checker.php (the backend code) and check.php (which contains HTML, PHP, and AJAX to asynchronously ping the server for checker.php).
Test this on various browsers and tabs to simulate multiple users. Close a few tabs and browser to see the effect.
The Setup
A database online_users is needed on your MySQL server. Two tables are needed in this database: online and highest. The table online should contain two columns: column id which could be an INT or VARCHAR depending on the way the ID’s of users are handled on your site but for the purpose of this illustration, it will be VARCHAR(10). The second column in table online should be time which should be a TIMESTAMP.
The second table highest should contain two columns; column time which is a TIMESTAMP that defaults to the current time on the server and column num which is an INT.
You can set these up manually using your phpMyAdmin or by using the code below assuming your database name is “online_user” and you have connected to the MySQL server using the variable $connection.
<?php
.
.
.
$sql = "CREATE DATABASE online_users";
mysql_query($sql, $connection);
$sql = "CREATE TABLE online_users.online (id VARCHAR( 10 ) NOT NULL ,
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE = InnoDB";
mysql_query($sql, $connection);
$sql = "CREATE TABLE online_users.highest (time TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP , num INT NOT NULL ) ENGINE = InnoDB";
mysql_query($sql, $connection);
.
.
.
?>
Please note that you have to create these database and tables before the source files included with this article can work. This section of database and table creation were left out of the source code so as to keep it clean, clear, and straight to the point.
The checking checker
checker.php contains the backend code but before usage you have to change the $db_host, $db_username, $db_password, $db_name variables to your MySQL host, username, password, and database name, respectively.
Challenge 1
The first step in achieving our aim is in logging the ID and the current time of every user present on the site into the “online” table of the database and to ensure this information is updated as long as the user still has the site opened in their browser. If this is achieved, the following problems are solved at once.
Solution 1:
- Get if a user is online: By checking the time the row of a user was last updated, you can tell if the user is still online or not. For instance, if you set the row to be updated every 30 seconds and the last time the row was updated was 1 minute ago or more, you can rightly say the user is no longer online.
- Get the number of users that are online: If you choose that a user can be considered offline if the time the row was last updated is 1 minute ago or more, then by getting the number of rows updated less than 1 minute ago, you get the number of users online.
- Last Seen: By checking the last time the row of a user was last updated, you can determine if he is online or the “Last Seen” time.
- Number of users online today: By getting the number of rows that has been updated that particular day, you get the number that has been online that day.
Challenge 2:
The second challenge (and last since Solution 1 solved 4 of 5) is how to get the highest online ever. Of course this will be at a “point in time”. This might look scary at first when you think a script might have to keep running on your server at all time so as to get the the number of users online every millisecond. That is not the solution anyway so relax!
Solution 2:
- Highest Online Ever: to have the highest online, you have to have users present online! This might first seem like a dump statement but it is true. So why not put the responsibility of checking for the “highest online ever” on online users instead of having Cron Jobs run a script perpetually on your server. If there are no users online then there is no need for checking the highest online.
When a user reports his presence, the number of online users is checked and compared with the column “num” of the table “highest”. If the number is greater or equal to “num”, the current time and number is INSERT INTO or UPDATEd into the table.
The pinging check
The check.php file contains PHP code, AJAX scripts, and HTML that runs on the frontend. This is just like your web page, i.e., what the user will see. AJAX is used to asynchronously communicate with the server every 30 seconds to report the presence of the user amidst others.
Codes and Explanation
Four out of our five challenges can be solved by the page simply reporting its presence to the server, say every 30 seconds, and the server appropriately updating the row in the table “online”.
- Reporting User’s Presence
.
.
.
if(isset($_GET[report]))
{
$query = "SELECT * FROM ". $db_name .".online WHERE id = '%s'";
$query = sprintf($query, mysql_real_escape_string(stripslashes($_GET[id])));
if(mysql_num_rows(mysql_query($query, $con)) > 0)
{
$query = "UPDATE ". $db_name .".online SET time = '%s' WHERE id = '%s'";
$now = date('Y-m-d H:i:s', strtotime('now'));
$query = sprintf($query, $now, mysql_real_escape_string(stripslashes($_GET[id])));
mysql_query($query, $con);echo $query;
}
else
{
$query = "INSERT INTO ". $db_name .".online (id, time) VALUES ('%s', '%s')";
$now = date('Y-m-d H:i:s', strtotime('now'));
$query = sprintf($query, mysql_real_escape_string(stripslashes($_GET[id])), $now);
mysql_query($query, $con);
}
setHighestOnline($con, $db_name);
}
.
.
.
This piece of code sits on the backend (checker.php) and checks if $_GET[report] is set(). If true, that indicates a page is reporting the presence of a user. The code then checks if the user has been registered into the table “online” before. If true (i.e., the number of rows returned in the query is greater than zero) the row is UPDATEd else the information is INSERT INTO the table.
Remember in Solution 2 it was stated that whenever a user presence is reported, the number of online users should be checked and appropriately handled, that is the reason for the setHighestOnline() function. The function will be discussed later.
The user reports the presence from check.php with this piece of AJAX code:
.
.
.
function doReport()
{
k = getXMLHttpRequestObject();
if(k != false)
{
url = "checker.php?report&id=" +
document.getElementById("userID").value;
k.open("POST", url, true);
k.onreadystatechange=function()
{
if(k.readyState==4)
{
}
}
k.send();
}
else
{
alert("Cant create XMLHttpRequest");
}
}
.
.
.
doReport();
setInterval(doReport, 30000);
.
.
.
This is just a simple AJAX code but notice that “POST” was used in the open method but the parameters were passed using GET method (i.e., appending the parameters to the URL). $_GET[] was also used on the backend code. The purpose of using POST in the open method is to prevent caching, GET will always cache if used in the open method and this is not desirable in this situation hence the POST. All the other AJAX codes simply follow this pattern. The parameter id in the variable URL above should be the UserID of the user. In this example, an ID is randomly generated and stored in a hidden input field.
.
.
.
<?php
echo "<input type=hidden id=userID value=" . rand(0, 5) . ">";
?>
.
.
.
In your own code use the real UserID.
Setting the highest online ever
.
.
.
$highest_online = 0;
function setHighestOnline($con, $db_name)
{
mysql_select_db($db_name, $con);
$query = "SELECT * FROM ". $db_name .".online";
$result = mysql_query($query, $con);
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_assoc($result))
{
if(abs((strtotime($row['time'])-strtotime(
date('Y-m-d H:i:s', strtotime("now"))))) < 60)
{
$highest_online++;
}
}
}
$query = "SELECT * FROM ". $db_name .".highest";
$result = mysql_query($query, $con);
if(mysql_num_rows($result) > 0)
{
$row = mysql_fetch_assoc($result);
if($highest_online >= $row['num'])
{
$query = "UPDATE ". $db_name .
".highest SET time = NOW(), num = '" . $highest_online . "'";
mysql_query($query, $con);
}
}
else
{
$query = "INSERT INTO ". $db_name .
".highest(time, num) VALUES(NOW(), '" . $highest_online . "')";
mysql_query($query, $con);
}
}
.
.
.
This will simply check for the number of users online from table “online” and compares the result with the record in table “highest”. Table “highest” is UPDATEd or INSERT INTO appropriately. The other function just follows this pattern of checking, updating, and inserting into the MySQL database and is pretty simple to comprehend. The setInterval() function in the JavaScript is used to run all the AJAX functions at a specified interval.
.
.
.
setInterval(doReport, 30000);
setInterval(numOnline, 60000);
setInterval(lastSeen, 60000);
setInterval(highestOnlineEver,(60000*5));
.
.
.
Other necessities
- Ensure both checker.php and check.php are in the same directory, else you will have to update the URL variable in the AJAX code appropriately.
- Note that the code assumes your connection to the database will never fail so no error handling was built in
- You can also extend the code to include a function too get the current page the user was viewing at a particular time. This and others were not included so as to keep the code focused, short, and clear. Though this can be featured as a Part Two on popular demand.
A screenshot
1st Revision: 9th of April, 2013
The basic algorithm still remains but the original code was altered a little to carter for some oversight
- The Daylight Time Saving behavior of
strtotime() of PHP: The +1 hour advance of time by strtotime() of PHP during DTS was not considered in the initial code. This led to the inability of the code to detect online users during DTS because all user online appear to have been offline for the past one hour. That was fixed by passing date('Y-m-d H:i:s', strtotime('now')) as the current time instead of using MySQL NOW(). - An unexpected behavior was also detected whereby function
setHighestOnline() was returning MySQL error "No Database selected". That was also fixed by passing an already connected MySQL link to the function. - Fixed some typo's.
