Home arrow MySQL arrow Page 2 - Display Users Online Based On Page Viewing
MYSQL

Display Users Online Based On Page Viewing


In today's article Stan shows us how to count the number of users browsing a particular page on our site using PHP and MySQL.

Author Info:
By: Stan Crawford
Rating: 5 stars5 stars5 stars5 stars5 stars / 10
September 18, 2002
TABLE OF CONTENTS:
  1. · Display Users Online Based On Page Viewing
  2. · Building The Script
  3. · The Code Explained (contd.)
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials