Users Online

armed rebel - 2006-07-05 19:10:12 in PHP
Category: PHP
Reviewed by: armed rebel   
Reviewed on: Jul 05 2006

If you've ever needed a guest/member counter for a website, then read this article and you'll be set. This article will go though setting up your MySQL database, adding records to you database and pulling those results out to display them. Note: This article assumes that you

Intro and Editing your Database

Before we start, we should get a general idea of what we are trying to accomplish. In the end, when will have two displayed numbers, and one list. One counter will be for any guests browsing the site and secondly any members browsing the site. Then, we will list all the members on the site. For the MySQL entries, we are going to have one "users_online" table, then rest of the details can be found later in the page. As for getting/updating the data, firstly we'll collect the user data into variables for easier input into the database. Next, we'll be checking that info against the database to determine if the user has been registered as online - if s/he is, then we update the information in the database, if not, then we input the first set of data into the database, and then we update it further. Finally we'll pull the information out of the database and display it.

Although you may edit the code as you wish, it is suggested that you keep all code as it is, and edit it after so there is little room for error.

The first step in creating a users online count is having a table to store and get the information from. Use the following MySQL query on your database of choice:

CREATE TABLE `users_online` (
`session_id` VARCHAR( 32 ) NOT NULL ,
`last_active` VARCHAR( 10 ) NOT NULL ,
`guest` ENUM( '0', '1' ) DEFAULT '1' NOT NULL ,
`user_id` BIGINT,
`ip_address` VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `session_id` ) ,
UNIQUE (
`ip_address`
)
) TYPE = MYISAM

Use your favorite method to run this query into your database, and continue.

(Note: If you feel you have an understanding of the above query, move on to the next page.)

This is a pretty basic query, so I won't go to in depth into it, but I'll run over a few key details.

`session_id` VARCHAR( 32 ) NOT NULL , The length of session ids is always 32, so we'll put that in a VARCHAR data type with a length of 32.

`last_active` VARCHAR( 10 ) NOT NULL , The length of all UNIX Timestamps are 10 digits, and will continue to be for the few centuries, so we are safe with 10. We could also use the INT data-type, but VARCHAR is fine. This same logic applies to the ip_address field as well, IP Addresses are all 15 characters in length.

`guest` ENUM( '0', '1' ) DEFAULT '1' NOT NULL , The guest type uses the data-type ENUM. ENUM is basically a list of options, and only one option can be selected. Here, we are just using a simple True/False (1/0) list. The default is set to True (1), and you'll see why later in the tutorial.

(Note: For more information on MySQL Data-types, visit this page.)

In the next step, we'll be storing and updating your user data.

Here is the entire code to storing data, and I'll go over the parts of it after. You'll be placing this wherever you use your counter.

<?php
$sess=session_id();
$now=time();
$check_user_online = mysql_num_rows(mysql_query("SELECT * FROM users_online WHERE sess_id='$sess'"));
if($check_user_online<=0)
{
    $create_session=mysql_query("INSERT INTO users_online (sess_id, last_active, ip) VALUES ('$sess', '$now', '$_SERVER[REMOTE_ADDR]')"); 
}
$check_user_online = mysql_num_rows(mysql_query("SELECT * FROM users_online WHERE sess_id='$sess'"));
if($check_user_online>=1)
{
    if(isset($_SESSION['user_id']))
    {
        $update_session=mysql_query("UPDATE users_online SET guest='0', last_active='$now', user_id='$_SESSION[user_id]' WHERE sess_id='$sess'");
    }
    else
    {
        $update_session=mysql_query("UPDATE users_online SET last_active='$now', guest='1', user_id='NULL' WHERE sess_id='$sess'");
    }
}
?>

Now letÂ’s go over it. Again, if you feel you have an understanding of the code, skip to the next page.

$sess=session_id();
$now=time();

These two simple lines are just setting up for inputting the data in the database. This script relies on sessions being set up, so if you haven't already, add a session_start(); to the top of any page which this counter will be set up. The session_id(); is a unique ID which PHP sets every new session. The time(); is a PHP function that retrieves a UNIX Timestamp that counts the seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

$check_user_online = mysql_num_rows(mysql_query("SELECT * FROM users_online WHERE session_id='$sess'"));
if($check_user_online<=0)
{
    $create_session=mysql_query("INSERT INTO users_online (sess_id, last_active, ip_address) VALUES ('$sess', '$now', '$_SERVER[REMOTE_ADDR]')"); 
}

This excerpt checks if your session has been registered and if not, it will add your data. The database row will first register the user as a guest (remember, the guest column defaults to 1), then, in the next step, it will update it to whether or not your user is a member.

$check_user_online = mysql_num_rows(mysql_query("SELECT * FROM users_online WHERE sess_id='$sess'"));
if($check_user_online>=1)
{
    if(isset($_SESSION['user_id']))
    {
        $update_session=mysql_query("UPDATE users_online SET guest='0', last_active='$now', user_id='$_SESSION[user_id]' WHERE session_id='$sess'");
    }
    else
    {
        $update_session=mysql_query("UPDATE users_online SET last_active='$now', guest='1', user_id=NULL WHERE sess_id='$sess'");
    }
}

This part updates the users_online table and updates it to whether you are a member or guest. The reason we query the database again, is because we are checking the data you may have just added, and without it would take two page loads to display a registered member on your site. You may change whatever session variable you are using that only a member would have, but for our use, it'll just be user_id. The bulk of this step is self explanatory so we'll move on to the next step.

So now we have added and updated all the users that browse your site, and we're ready to display them. You can essentially change how it displays to how you want, but to simplify things I'll just create a line for the counts and show the users "list-style".

<?php
$now=time();
$cut_off = $now - 300;
$members_sql = "SELECT DISTINCT user_id FROM users_online WHERE guest='0' AND last_active >= '$cut_off'";
$members_query = mysql_query($members_sql);
$members = mysql_num_rows($members_query);
$guests = mysql_num_rows(mysql_query("SELECT DISTINCT sess_id FROM users_online WHERE guest='1' AND last_active >= '$cut_off'"));
?>
 
<b>Users Online</b>
Members Online: <?=$members?> -- Guests Online: <?=$guests?>
 
<?php
while($user = mysql_fetch_array($members_query))
{
    $user = mysql_fetch_array(mysql_query("SELECT username FROM users WHERE user_id='$user[user_id]'"));
    echo "$user[username]<br />";
}
?>

There isn't much else to say here as the script above speaks for itself. Again, the time() function gets the seconds since the Unix Epoch, then for the $cut_off variable, we simply subtract 5 minutes (5 * 60 = 300) from that count. You can change this to what you want. Secondly, we need to only select the rows that have different user_ids stored (if we didn't, then it's possible to get one user showing up multiple times on the list), so we use the DISTINCT MySQL function.

Note: When looking at the above script, you can see that there are two ""s. This is a quick, easy way to display PHP variables without having to create a new PHP section and it gives a cleaner code look.

Lastly, before you can call your users online script complete, you need a way to delete old records so you don't fill up 10,000 rows when you'll likely only need to use the newest 1%. This last part is an extremely simple 2 line script. If you know how and prefer it, you could set this as a CRON job, so wouldn't have to worry about it, but this way is just as simple and worry-free.

$maxtime = time() - 301;
mysql_query("DELETE FROM users_online WHERE last_active < '$maxtime'");

I find putting this under where you put the database updating script from Page 2 works fine. You can also increase the $maxtime to something more "safe", but it's not needed.

Conclusion

And this concludes my tutorial on displaying users online. If you have questions or comments on this or any tutorial of mine, feel free to email me using this form.