This tutorial will demonstrate how you can create a very simple 'Users online' script that will tell you how many users are browsing your website and what page they are on.
First we create a table:
CREATE TABLE IF NOT EXISTS `users_online` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(20) NOT NULL,
`timestamp` varchar(20) NOT NULL,
`page` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
You can either execute this in your preferred MySQL administration tool (e.g. phpMyAdmin), or execute it in PHP: mysql_query($create); where $create is equal to the above SQL query. The timevisited column will take a timestamp (i.e. time()) in case you're wondering.
Updating the list
Now we'll create a function named usersOnline($time_span) that takes an input $time_span, which is the amount of minutes you wish to keep the user in the database or 'list' before they are removed. A realistic duration would be around 5-10 minutes.
function usersOnline($time_span) {
$time_criterion = time()-($time_span*60);
$clean = mysql_query("DELETE FROM `users_online` WHERE `timevisited` < '$time_criterion'") or die (mysql_error());
}The function begins by taking the time span and converting it into a criterion that will be used for comparison in the $clean query ($time_span*60 simply converts the time span into seconds). Then the $clean query deletes any rows in the users_online table that are older than the specified time span.
function usersOnline($time_span) {
$time_criterion = time()-($time_span*60);
$clean = mysql_query("DELETE FROM `users_online` WHERE `timevisited` < '$time_criterion'") or die (mysql_error());
$ip_address = fetchIpAddress();
$check = mysql_query("SELECT * FROM `users_online` WHERE `ip` = '$ip_address'") or die (mysql_error());
}Next we grab the user's IP address and check if they are already in the database. The function fetchIpAddress() is defined below:
function fetchIpAddress() {
if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip_address=$_SERVER['HTTP_CLIENT_IP'];
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ip_address=$_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
$ip_address=$_SERVER['REMOTE_ADDR'];
}
return $ip_address;
}
The function checks for the users true IP address, so if that user is using a proxy it will grab that IP address.
We then store the number of rows returned by the $check query in $rows, define the current timestamp and the page the user is on (the page's path rather).
function usersOnline($time_span) {
$time_criterion = time()-($time_span*60);
$clean = mysql_query("DELETE FROM `users_online` WHERE `timevisited` < '$time_criterion'") or die (mysql_error());
$ip_address = fetchIpAddress();
$check = mysql_query("SELECT * FROM `users_online` WHERE `ip` = '$ip_address'") or die (mysql_error());
$rows = mysql_num_rows($check);
$current_timestamp = time();
$page = mysql_real_escape_string($_SERVER['REQUEST_URI']);
}Next step is to handle the cases where the user is or is not in the database. If the user is not in the database, then we insert a new row with the details defined above. If the user is in the database, we simply update the existing row with the page the user is on $page and the new (updated) timestamp $current_timestamp:
function usersOnline($time_span) {
$time_criterion = time()-($time_span*60);
$clean = mysql_query("DELETE FROM `users_online` WHERE `timevisited` < '$time_criterion'") or die (mysql_error());
$ip_address = fetchIpAddress();
$check = mysql_query("SELECT * FROM `users_online` WHERE `ip` = '$ip_address'") or die (mysql_error());
$rows = mysql_num_rows($check);
$current_timestamp = time();
$page = mysql_real_escape_string($_SERVER['REQUEST_URI']);
if ($rows == 0) {
$insert = mysql_query("INSERT INTO `users_online` (`ip`, `timevisited`, `page`) VALUES ('$ip_address', '$current_timestamp', '$page')") or die (mysql_error());
} else {
$update = mysql_query("UPDATE `users_online` SET `timevisited` = '$current_timestamp', `page` = '$page' WHERE `ip` = '$ip_address'") or die (mysql_error());
}
}It is very important to escape the $page variable as you would otherwise be vulnerable to SQL injection when executing your query, and so we use mysql_real_escape_string() to do so.
Displaying the list
Now we want to show what's in our table and we do so by creating a function called showUsersOnline($show_list) which takes the input $show_list, giving you the option to either show the number of users online, or show both the number and additional information such as IP address, page, time etc.
We begin by printing the number of users that are online (rows returned by the $check query):
function showUsersOnline($show_list) {
$check = mysql_query("SELECT * FROM `users_online` ORDER BY `timevisited` DESC") or die (mysql_error());
$rows = mysql_num_rows($check);
echo "Users online: $rows";
}Next we display a list of users that are online and what page they are on:
function showUsersOnline($show_list) {
$check = mysql_query("SELECT * FROM `users_online` ORDER BY `timevisited` DESC") or die (mysql_error());
$rows = mysql_num_rows($check);
echo "Users online: $rows";
if ($show_list) {
$count=1;
while ($useronline = mysql_fetch_array($check)) {
echo "<br />• $count. ($useronline[ip]) Browsing page: <a href="$useronline[page]">$useronline[page]</a>";
$count++;
}
}
}We store the data in each row in an array $useronline and loop through the results. The order of results is really up to you; in this example I've ordered them by timevisited DESC - in order of the most recent visit/activity (which would always be yourself). $count is simply an increasing counter to number the results/list, this can be done in many other ways and you don't necessarily have to do it like I have done here. You'll notice that if ($show_list) precedes the part of the code that shows the list; with this function you will have the option to specify whether you want the list to be shown or not. For example, if you would prefer that guests/normal users can only see the number of users online and not the list (as this contains private information such as the IP address), then you would call showUsersOnline(0);. If the user is an administrator with certain access privileges, then you would call showUsersOnline(1);. Of course you could just omit the IP address from the list in the function above and display it for both administrators and normal users.
You might be thinking, well what if $check returns zero results? Wouldn't I need to place an if statement before displaying the list of users online? Well in the above case, no. In order to view the list, a user must be browsing on the website and so there will always be a row in the users_online table.
So we end up with three functions, ideally placed in some sort of functions file:
<?php
function usersOnline($time_span) {
$time_criterion = time()-($time_span*60);
$clean = mysql_query("DELETE FROM `users_online` WHERE `timevisited` < $time_criterion") or die (mysql_error());
$ip_address = fetchIpAddress();
$check = mysql_query("SELECT * FROM `users_online` WHERE `ip` = '$ip_address'") or die (mysql_error());
$rows = mysql_num_rows($check);
$current_timestamp = time();
$page = $_SERVER['REQUEST_URI'];
if ($rows == 0) {
$insert = mysql_query("INSERT INTO `users_online` (`ip`, `timevisited`, `page`) VALUES ('$ip_address', '$current_timestamp', '$page')") or die (mysql_error());
} else {
$update = mysql_query("UPDATE `users_online` SET `timevisited` = '$current_timestamp', `page` = '$page' WHERE `ip` = '$ip_address'") or die (mysql_error());
}
}
function fetchIpAddress() {
if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip_address=$_SERVER['HTTP_CLIENT_IP'];
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ip_address=$_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
$ip_address=$_SERVER['REMOTE_ADDR'];
}
return $ip_address;
}
function showUsersOnline($show_list) {
$check = mysql_query("SELECT * FROM `users_online` ORDER BY `timevisited` DESC") or die (mysql_error());
$rows = mysql_num_rows($check);
echo "Users online: $rows";
if ($show_list) {
$count=1;
while ($useronline = mysql_fetch_array($check)) {
echo "<br />• $count. ($useronline[ip]) Browsing page: <a href="$useronline[page]">$useronline[page]</a>";
$count++;
}
}
}
?>
You would then include this in your main page (before the main body) after establishing a connection to your database, and then if you wish, display the results. Below is a very basic example:
<?php
include_once 'config.php'; //This file would contain the variables needed to connect to the database with $link, below
include_once 'functions.php'; //We include the functions we have created
$link = mysql_connect($server, $db_user, $db_pass)or die ("Could not connect to mysql because ".mysql_error());
mysql_select_db($database)or die ("Could not select database because ".mysql_error());
usersOnline(5); //We call the usersOnline function with a time span of 5 minutes
showUsersOnline(1); //Show the number of users online, and the list of users
mysql_close($link);
?>
That ends this tutorial. As always if you notice any errors or have anything to say/ask, drop me a comment below.
Update: I was just reading over this tutorial and thought that it may be better to place $clean (and if you want, $time_criterion) at the end of the function - that way if the user (their IP) exists in the database, a single update is performed rather than deleting the user and inserting a new row; decreasing overall execution time of the function.
| January 12, 2012 |