You are here: Home > Scripts > PHP MySQL User Counter


	What's New	Doll Makers	Room Makers	Puzzle Makers	Roiworld	Stardoll	Smilies	Links

Back to Scripts

User Count, Display Today and Totals

(requirements: PHP and MySQL)
(Note: This script is exactly the one used on this page.)

See also Users Online Script
This script uses very little overhead and gives an accurate and unique count of the visitors to this web page. It also tracks the Total number of visitors (the sum of each day's total) that came to this page.

To extend this script to your whole site just add this script to each page you wanted included <?php include'usercounter.php'?>

Corrected SQL query when clearing rows at the end of the day, v1.1
It is recommended to place the script within a table for aesthetic reasons, see below.

<table background="images/counter.png" width="100" height="50">
<tr><td><?php include'usercounter.php';?></td></tr>
</table>

Here is the image used, 100 by 50

<?php
// Configuration
$dbhost = "localhost";
$dbuser = "[to-add]"; // MySQL Username
$dbpass = "[to-add]"; // MySQL Password
$dbname = "[to-add]"; // Database Name

$today=date("d");
$ip = substr($REMOTE_ADDR, 0, strrpos($REMOTE_ADDR,"."));

// Connect to MySQL Database
@mysql_connect($dbhost,$dbuser,$dbpass);
@mysql_select_db($dbname);

// Add this user to database
mysql_query("insert into usercountertoday(ip,day) values('$ip','$today')");

// check date on usercountertotal
$result = mysql_query("select * from usercountertoday");
$count = mysql_num_rows($result);
@mysql_free_result($result);

$result = @mysql_query("select * from usercountertotal");
$col = mysql_fetch_array($result, MYSQL_NUM);
$totalday = $col[0];
$totalsum = $col[1];

if ($totalday != $today)
{
mysql_query("delete from usercountertoday where day='$totalday'");
mysql_query("delete from usercountertotal where day='$totalday'");
$totalsum += $count;
@mysql_query("insert into usercountertotal(day,total) values('$today','$totalsum')");
}

mysql_free_result($result);
@mysql_close();

// Show today and total
echo $count."<br/>".$totalsum;

?>

A quick overview of the script:

Retrieve the first 3 tuples of the IP (nnn.nnn.nnn)
Check total, is it a new day
Display today and total counts

1. Why the first 3 tuples of an IP?

Because of proxies, like the AOL proxy, AOL users daisy chain through a set of IP's that belong to a proxy. Each HTTP request would use an IP from a pool of IP's. Normally an IP address remains static per session, now with proxies, every single http request could be on a different IP, however these IP's mostly belong to a specific subnet, thus we retrieve the first 3 tuples of the IP.

2. Check total

The script does a quick check to see if we are a new day. The date is a two digit day value ranging from 01-31. If true then clear the tables 'usercountertotal' and 'usercountertoday'. Update the usercountertotal with the latest summation.

3. Display counter, Today and Total

Today - count for the number of unique users that visited today
Total - The sum of all unique users since the counter was started till yesterday.

Create SQL table

Be sure to create your table first before running the script
CREATE TABLE `usercountertoday` ( `ip` VARCHAR( 15 ) NOT NULL , `date` VARCHAR( 2 ) NOT NULL , UNIQUE ( `ip` ));

CREATE TABLE `usercountertotal` ( `day` VARCHAR( 2 ) NOT NULL , `total` VARCHAR( 18 ) NOT NULL );
Easy to extend

You can add an additional field in the table, 'usercountertoday' to mark the hour. This way you could quickly generate a graph showing number of people per hour. Additionally other information such as the referrer, country code page entered by can be used to provide a more detailed snapshot of activity. In my case all I wanted was a today counter and a totals counter.

About Privacy Policy Hosting by Web Hosting Canada

Except where otherwise noted, this site is licensed under a Creative Commons License