Counting the number of users that are currently browsing your site is a great way to brag about your high number of unique visitors if you're running a busy web site. In this article Joe shows us how to use PHP and a MySQL database to show the common "There are currently 23 users online" text on our web site. He uses two very simple scripts to do this, and the result is small, quick and informative. If you run your own site, then be sure to check this article out!
Finding How Many Users Are On Your Site With PHP - Building the database (Page 2 of 5 )
Our users online script will obviously display how many people are online, but because the HTTP protocol is stateless, so is PHP in a sense. If you're familiar with ASP, then you will know about the global.asa file, which allows you to run events when a user both enters and leaves your site. ASP tracks this with a session timeout, which is 20 minutes by default. When it "detects" that a users session has ended, then code inside a function called session_onEnd() is called.
Unfortunately, because we don't have this kind of functionality available to us with PHP, we're going to have to create a work around to get past it. Here's what we're going to do:
We will assume that a session is 20 minutes in length.
When a user requests a page from our site, we will check our MySQL table (which we will create shortly) for an entry containing that users IP. If one is found within the last 20 minutes, then we don't add one. On the other hand, if no entry is found in the database, then we add one with the users IP address and current date/time.
To display the number of users online, we select a count of all records in our database where the time is within the last twenty minutes.
But first thing's first. Let's create our database. Fire up the MySQL console application and enter the following code:
create database siteStats; use siteStats;
create table usersOnline ( id int auto_increment not null, userIP varchar(20) not null, dateAdded timestamp, primary key(id), unique id(id) );
Our database contains just one table called usersOnline. UsersOnline contains three fields:
id: An automatically incremented ID field, which makes sure each entry in the usersOnline table is unique.
userIP: The IP address of the user, i.e. 220.127.116.11.
dateAdded: A timestamp of when the user visited our site. It's stored in the format of yyyymmddhhmmss, i.e. 20020428121244. We will use MySQL's UNIX_TIMESTAMP function later to get it into a different format.
When we add a record to our usersOnline table, we only need to specify the IP address, because both the id and dateAdded fields automatically update themselves.
OK, now that we understand how our users online system will work, let's create the simple PHP scripts that will add users to the database and also show how many are online.