Display Users Online Based On Page Viewing - Building The Script

(Page 2 of 4 )

In all honesty, this script is a lot simpler than one might think -- It simply connects to a database and updates a few fields that are then counted back to the user, providing them with the number of users online. Let's take a look at how the tables for the database are set up:

CREATE TABLE useronline (
timestamp int(15) DEFAULT '0' NOT NULL,
ip varchar(40) NOT NULL,
file varchar(100) NOT NULL,
PRIMARY KEY (timestamp),
KEY ip (ip),
KEY file (file)
);

The timestamp is used to display the current time that the user landed on a specific page on your web site. In addition, the IP address of each user is also logged to make sure identical entries are not added to the database. The File field is simply the PHP_SELF function that gives the path of the script currently running. On my machine it simply looks like /stan/www/php/script.php.

I wont go into detail about the last three fields –- it's sufficient to know that they're required for error checking and reliability.

Let's now take a look at how the PHP script works with the database to track how many users are visiting a particular page on our web site.

It's Alive!
Here's the PHP code. We will first take a look at the script and then break it down into sections which I will discuss:

<?php

$server = "xxx";
$db_user = "xxx";
$db_pass = "xxx";
$database = "xxx";
$timeoutseconds = 300;

$timestamp = time();
$timeout = $timestamp-$timeoutseconds;

mysql_connect($server, $db_user, $db_pass);
$insert = mysql_db_query($database, "INSERT INTO useronline VALUES ('$timestamp','$REMOTE_ADDR','$PHP_SELF')");

if(!($insert)) {
print "Useronline Insert Failed > ";
}

$delete = mysql_db_query($database, "DELETE FROM useronline WHERE timestamp<$timeout");

if(!($delete)) {
print "Useronline Delete Failed > ";
}

$result = mysql_db_query($database, "SELECT DISTINCT ip FROM useronline WHERE file='$PHP_SELF'");

if(!($result)) {
print "Useronline Select Error > ";
}

$user = mysql_num_rows($result);

mysql_close();

if($user == 1) {
print("<b>$user</b> user online\n");
} else {
print("<b>$user</b> users online\n");
}

?>

The beginning of the script is the basic information that you need to connect to your MySQL database –- you should replace the "xxx" values with those of your MySQL database. The one variable in particular that we are interested in is $timeoutseconds = 300. This variable subtracts seconds over time against the timestamp and tells the timeout variable when to "time out". This is used later in the script to delete users when 300 seconds (5 minutes) has lapsed.

Simplicity Is Best
The first line of code below the variable settings is an insert statement:

$insert = mysql_db_query($database, "INSERT INTO useronline VALUES ('$timestamp','$REMOTE_ADDR','$PHP_SELF')");

if(!($insert)) {
print "Useronline Insert Failed > ";
}

The code above simply connects to your database and then inserts the values into the useronline table. The next part of the code looks at the database and deletes the records for expired users (i.e. their timestamp is older than 300 seconds):

$delete = mysql_db_query($database, "DELETE FROM useronline WHERE timestamp<$timeout");

if(!($delete)) {
print "Useronline Delete Failed > ";
}

The code above demonstrates a good housing keeping technique and gives fairly accurate results when the actual number of users online is retrieved from the database. If this method was not used then your table would grow in size every time a new user accessed your page, and trust me, this can easily turn into several hundred thousand rows if you run a fairly busy web site.

Next: The Code Explained (contd.) >>
 

More MySQL Articles
More By Stan Crawford

Comments On: Display Users Online Based On Page Viewing

>>> Be the FIRST to comment on this article!