Perfect
One of the primary aims of most people with the responsibility of maintaining a web site is to promote it in order to attract as many visitors to the site as possible. There are plenty of techniques and methods for doing this but that's not what this tutorial is about. Instead, this tutorial will teach you how you can create your own user counter which will allow you to see how many people are currently on your web site. This is a piece thing to know if you are interested in just how many people use your site at any given time.
Table of contents
1. The idea
Okay, so what exactly are we going to be doing here? Well the aim is to be able to count how many users are currently on your web site so in order for us to do this we need to be tracking them somehow. By using a MySQL database and just a single table we can do exactly that.
For this to work you will need access to a MySQL database - if you do not have this then you should contact your host and find out if they can provide this for you.
2. Set up the database
Once you've got your database created and ready (whether you or your host does it) it's now time to get to work on our table. Before creating any tables you should ask yourself exactly what data you need it to store. In the case of this script you will need to store just two things: the visitors IP address and the time they last visited.
In order to create this table you can either do it manually using the table creation section of phpMyAdmin or alternatively you can just run the following SQL query on your database.
CREATE TABLE `user_online`
(
`ipaddress` VARCHAR( 15 ) NOT NULL,
`lastactive` INT( 10 ) NOT NULL,
PRIMARY KEY ( `ipaddress` )
)
The above query will create a table called "user_online" which has two columns - ipaddress and lastactive. The ipaddress column has been give the VARCHAR data type which means it accepts a string of all characters of up to 15 in length. This is needed because a typical IP address in the form of xxx.xxx.xxx.xxx is 15 characters long. The lastactive colum has been given the INT data type which basically means it can accept integers with a maximum length of 10.
Now that we have created our table now is a good time to take note of a few things. Firstly you will need to know the name of your database - for this tutorial mine is called "mydatabase" but yours will most likely be different. You will also then need the username and password for that database. These are needed so that our PHP script can connect to the MySQL database. And then finally you will need to know your server name but this isn't vital - usually you can substitute it with "localhost" and it should work just fine.
3. The main code
Now that the database is ready we can now start looking at the PHP code which will make up our user tracking script. Take a look at the following piece of code which should be saved as "online.php" or something similar.
// Database connection and selection
mysql_connect("localhost", "root", "") or die("Cannot connect");
mysql_select_db("mydatabase") or die("Cannot select database");
// Query for the visitors IP address
$ipQuery = mysql_query("SELECT * FROM user_online WHERE ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "' LIMIT 1");
// Have they visited before?
if(mysql_num_rows($ipQuery) == 1)
{
// They have visited before
// Update last active time
mysql_query("UPDATE user_online SET lastactive = " . time() . " WHERE ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "' LIMIT 1");
}
else
{
// They have not visited before
// Insert new row
mysql_query("INSERT INTO `user_online` VALUES ('" . $_SERVER['REMOTE_ADDR'] . "', " . time() . ")") or die(mysql_error());
}
// Delete any old records (where lastactive < NOW - 5 Mins)
mysql_query("DELETE FROM user_online WHERE lastactive < " . (time() - 300)) or die(mysql_error());
// Query all views
$allViewQuery = mysql_query("SELECT * FROM user_online");
// Get the row count
$onlineCount = mysql_num_rows($allViewQuery);
echo "There is currently " . $onlineCount . " user(s) online";
The above code is our entire user tracking script. Now let me take you through it.
In line number 2 we use the mysql_connect() function. This function allows PHP to connect to the MySQL server so that it can then access its databases. This function requires three parameters which are the servers address (although "localhost" usually does the trick), the servers username (in this case "root") and the servers password (in this example it's been left blank). All three of these details can be obtained by speaking to your host.
We then on line 3 have the mysql_select_db() function. Similarly to mysql_connect(), this function is required if you wish your script to connect to a MySQL database. Where the previous function connects to the server this function takes care of selecting which database to use. It requires only one parameter (although you can provide others for slightly more advanced use) which is the name of the database which you would like to connect to (my database is called "mydatabase"). Again if you are unsure of the names or if you do not even have access to any MySQL databases get in touch with your host.
Now that our script has connected to the database we can now start working on the main section of our script. Now the logic behind this script can be explained like this.
Has the user visited before?
> If yes
Update users last active time in database
> If no
Insert new users details into database
Remove any old records which are now unnecessary
Output number of online users
So as you can see the first thing to do (apart from connecting to the database) is to use an if statement to determine if the person visiting is new or if they have visited before. This is done by querying our table using an SQL query and the mysql_query() and pull all rows where the ipaddress column is equal to the current users IP address. If a row exists then we need to do another query to update that rows lastactive column. If however no rows are returned then we need to insert the current visitors IP address and the time into the table as a new row.
Our initial query and the if-else statement is as follows.
$ipQuery = mysql_query("SELECT * FROM user_online WHERE ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "' LIMIT 1");
// Have they visited before?
if(mysql_num_rows($ipQuery) == 1)
{
// They have visited before
// Update last active time
mysql_query("UPDATE user_online SET lastactive = " . time() . " WHERE ipaddress = '" . $_SERVER['REMOTE_ADDR'] . "' LIMIT 1");
}
else
{
// They have not visited before
// Insert new row
mysql_query("INSERT INTO `user_online` VALUES ('" . $_SERVER['REMOTE_ADDR'] . "', " . time() . ")") or die(mysql_error());
}
In the code above we have used the mysql_num_rows() function - this function counts the number of rows which the supplied query returned which we can then use in our if-else statement. That's the 'tracking' part over and done with.
We could just go straight ahead and count all the rows in the table to determine how many people have visited - but that wouldn't necessarily show how many are actually on our site. To do this we need to remove any rows which are too old (more than 5 minutes old). To do this we simply perform another query on our user_online table and delete all rows where the lastactive column < NOW - 300. The number 300 is used because there are 300 seconds in 5 minutes. This is done in this line.
mysql_query("DELETE FROM user_online WHERE lastactive < " . (time() - 300)) or die(mysql_error());
Brilliant. Now we can move on and find out how many people have visited our script in the last 5 minutes, which is probably the easiest way to determine how many people are currently on your site.
To do this we do a simple query and pull all rows from our table. The number of rows returned equals the number of users online.
// Query all views
$allViewQuery = mysql_query("SELECT * FROM user_online");
// Get the row count
$onlineCount = mysql_num_rows($allViewQuery);
echo "There is currently " . $onlineCount . " user(s) online";
4. Implementing it
As far as our tracking script goes we're all done. All that's left to do is to implement it into your web site so you can see how many users are currently online.
There are two possible ways of doing this. Firstly we could use the PHP include() function and include our online.php script into all of our PHP enabled web pages. This can be done with just one line like this.
include('online.php');
Alternatively, if your web page is not PHP enabled then you can include it using an iframe. This can be done by using the following HTML code.
<iframe src="online.php"></iframe>
That's it - you should now be able to keep a track on how many people are currently on your site. Enjoy!
You can download the source code to this script here.
You must be a registered user to add your comments.
9-6-2008 Thanks for the feedback.
8-6-2008 Nicely written tutorial! No a days I go around and I find codes that are just to copy and paste, people don't usually explain them like you have, nice work, and good tutorial.