Author: Sazzad Hossain
Language: PHP
Demo: http://jorsek.info/stats/
Demo 2: http://jorsek.info/tcounter/
Zymic Demo: http://sazzad.99k.org/
Summary: In this tutorial, we will learn how to create a very simplified counter script. Unlike the general counter scripts, here we will also have a general page view counter, unique view counter, total users online and users viewing current page.
Step 1: db.sql
Lets write our database information first. This help out with understanding the rest of the items we will talk about later on in this tutorial. For this tutorial we will have 2 tables created. The first will be for the general display number of page views and unique views (this is displayed in the stats table). Next we will use a separate table for the users online under the usersonline table. It will take some time to explain all the little details, so if you want to know what they are meant to do, then go here.
CODE
CREATE TABLE `stats` (
`ip` varchar(15) NOT NULL default '',
`visits` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `useronline` (
`timestamp` int(15) NOT NULL default '0',
`ip` varchar(40) NOT NULL default '',
`file` varchar(100) NOT NULL default '',
PRIMARY KEY (`timestamp`),
KEY `ip` (`ip`),
KEY `file` (`file`)
) TYPE=MyISAM;
`ip` varchar(15) NOT NULL default '',
`visits` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `useronline` (
`timestamp` int(15) NOT NULL default '0',
`ip` varchar(40) NOT NULL default '',
`file` varchar(100) NOT NULL default '',
PRIMARY KEY (`timestamp`),
KEY `ip` (`ip`),
KEY `file` (`file`)
) TYPE=MyISAM;
Step 2: config.php
The main purpose of this file is to make our life a lot easier. This file will help store all the databse connection information. Instead of writing the following lines in every single file we are going to use it in, we are just going to include this entire file through the use of the includes function. So lets list all the connection information
CODE
<?php
$host = 'localhost'; // your host
$username = 'databse_username'; // databse username
$password = 'password'; // database password
$db_name = 'database_name'; // database name
?>
$host = 'localhost'; // your host
$username = 'databse_username'; // databse username
$password = 'password'; // database password
$db_name = 'database_name'; // database name
?>
The $host will help us list the type of host you are connecting to. In general we use localhost as our default host, but it may differ. In such case, contact your server admin to gain that information. Next we are going to use $username to input users name for the database. Next the $password is used to connect to that users account. Finally we are going to input the name of the database in the $db_name. Often people tend to use the database's name first, but it really doesn't make much of a difference.
Step 3: stats.php
Ok now we want to write a script that will count the number of current page views and unique views. In order to do that we need to first get the connection info for the database. Note that we already defined all of the database connection information in config.php, so now all we have to do is connect to that folder. In addition, we want to define which table in the database all information should be gathered and stored into. We can achieve that by:
CODE
<?php
include "config.php";
$tbl_name = 'stats';
include "config.php";
$tbl_name = 'stats';
Next we want to connect to the database itself. If we don't connect to the databse, all our hard work will go down the drain. So lets use the following code to connect to the database
CODE
$sql_connect = mysql_connect("$host", "$username", "$password") or die("MySQL Connection Failed: " . mysql_error());;
$db_connect = mysql_select_db("$db_name")or die("Could not select DB: " . mysql_error());;
$db_connect = mysql_select_db("$db_name")or die("Could not select DB: " . mysql_error());;
Now that all the easy work is done, lets get down to writing the actual script. To be honest, this is a pretty simple job. In php there is a built in function called $_SERVER['REMOTE_ADDR'];. This function allows us to gain the visitors IP address and store it somewhere.
CODE
$ip = $_SERVER['REMOTE_ADDR'];
Now we need to select the IP field and row of our SQL table.
CODE
$fetch = mysql_query("SELECT ip FROM $tbl_name WHERE ip ='".$ip."'") or die(mysql_error());
if ( mysql_num_rows($fetch) == 0 )
if ( mysql_num_rows($fetch) == 0 )
For a brief description, the $fetch is supposed to select the row if it exists in our database. If the output of the $fetch equals 0 (zero) that means that the row does not exist.
In such case where the value does not exist, we need to insert the visitors IP address into the IP and visits row of the table. We can do that by with the following line
CODE
{
mysql_query("INSERT INTO $tbl_name(ip,visits) VALUES('$ip','1')") or die(mysql_error());
}
mysql_query("INSERT INTO $tbl_name(ip,visits) VALUES('$ip','1')") or die(mysql_error());
}
If the users IP was found already (say he/she visited the site already), we need to add his new visit in our visits row next to his previously recorded IP. We can achieve that with an else statement and the line that follows
CODE
else {
mysql_query("UPDATE $tbl_name SET visits=visits+1 WHERE ip = '$ip'") or die(mysql_error());
}
mysql_query("UPDATE $tbl_name SET visits=visits+1 WHERE ip = '$ip'") or die(mysql_error());
}
Now we need to bring all the recorded information back to one area so that we can display it on our site. We can do that with the following series of lines.
CODE
$count = mysql_query("SELECT * FROM $tbl_name") or die(mysql_error());
This line of code helps us select our SQL table and if it fails, it will print out an error.
Now that we have that done, we need to count the number of rows in our table and and store it as $unique. This basically works almost exactly like our else statement does. If the persons IP address exists in our databse, it will add 1 to the visits row, if it does not, it will create a new field and add 1 to the new IP since it was their first visit. So lets put that in our code
CODE
$unique = mysql_num_rows($count);
Now we want to get the total number of visits. We can do that using a while statement. While statements are very useful in php. Lets get what we are looking for by using the following codes
CODE
while ($vi = mysql_fetch_array($count)) {
$visits = $visits + $vi['visits'];
}
$visits = $visits + $vi['visits'];
}
Ok now that we have almost everything done, we need to display that all in page. We can do that by echoing the $visits and the $unique. So lets give you a nice clean look for it. (Do note that we are also ending our php code, so we need to add in that end php line)
CODE
echo "Total Hits: " . $visits . "<br />";
echo "Unique Hits: " . $unique;
?>
echo "Unique Hits: " . $unique;
?>
Step 4: useronline.php
Many people may think this will be very hard to do. Well they are partially correct. In some sense it is hard, but compared to the stats.php file we just wrote, this is a piece of cake.
For this file, we also have to gain the database information, connect to sql server and open the correct table. Since we have already learned this before, its best we don't copy and paste the same information. So we'll just write the code in. If you need the explanation, just scroll up.
CODE
<?php
include "config.php";
$timeoutseconds = 300; // How long till it will remove the user from the database(In seconds)
$timestamp=time();
$timeout=$timestamp-$timeoutseconds;
// Connect to MySQL Database
mysql_connect($host,$username,$password);
@mysql_select_db($db_name) or die("Unable to select database");
include "config.php";
$timeoutseconds = 300; // How long till it will remove the user from the database(In seconds)
$timestamp=time();
$timeout=$timestamp-$timeoutseconds;
// Connect to MySQL Database
mysql_connect($host,$username,$password);
@mysql_select_db($db_name) or die("Unable to select database");
Ok I know we never added $timeoutseconds in the previous code. Yeah sorry about that, but it makes life a little easier. So simplest explanation of what that does is that it will count the time (in seconds) needed till it will remove the user from the database. So if a person visits your website, his IP will be stored in the database for that amount of time. The others are fairly self explaining.
Next we need to add the user to the database (since he doesn't exist yet). So we use this line
CODE
mysql_query("insert into useronline values('$timestamp','$REMOTE_ADDR','$PHP_SELF')") or die("<b>MySQL Error:</b> ".mysql_error());
Now we need to delete the users that have been online for more than the $timeoutseconds we set up, helps out when a person goes idol and forgets to close the browser he had opened.
CODE
mysql_query("delete from useronline where timestamp<$timeout") or die("<b>MySQL Error:</b> ".mysql_error());
Since that is done, we can now get the total users online from the database. We can achieve that by
CODE
$result = mysql_query("select distinct ip from useronline") or die("<b>MySQL Error:</b> ".mysql_error());
$user = mysql_num_rows($result);
$user = mysql_num_rows($result);
Now that we got the total users online, we want to get the number of users on the current page. To do this we will use
CODE
$resulta = mysql_query("select distinct ip from useronline where file='$PHP_SELF'") or die("<b>MySQL Error:</b> ".mysql_error());
$usera = mysql_num_rows($resulta);
mysql_close();
$usera = mysql_num_rows($resulta);
mysql_close();
Finally its time for to display all users online. We can do this by echoing the $user
CODE
// Show all users online
if ($user==1) {
echo"Users Online: $user";
}
else {
echo"Users Online: $user";
}
// Show users on this very page
if ($usera==1) {
echo"<br>Viewing This Page: $user</font>";
}
else {
echo"Viewing This Page: $user";
}
?>
if ($user==1) {
echo"Users Online: $user";
}
else {
echo"Users Online: $user";
}
// Show users on this very page
if ($usera==1) {
echo"<br>Viewing This Page: $user</font>";
}
else {
echo"Viewing This Page: $user";
}
?>
Step 5: Displaying
Ah finally the last and final step. This is real simple, all we want to do is include those two pages on our index or a page of our choice. Since we really didn't add any styles to it, we can simple include it on any page. We can do this by using the includes function. If you don't know what the include function does, then read this tutorial on php includes. So here is a really simplified code for it.
CODE
//Display general stats
<?php include("stats.php"); ?>
//Display online users
<?php include("useronline.php"); ?>
<?php include("stats.php"); ?>
//Display online users
<?php include("useronline.php"); ?>
Before we end, I would like to say that I read a tutorial on how to do things such as this, I have been working on perfecting this script for a little time, taking out little bugs and so far this is the final version. If you notice a bug, please do report it to me. If I made a mistake post here and say "Hey I think you did this wrong" or "you are missing this." Thank you for reading.
DOWNLOAD
If you are lazy like me you can download the file below. (Archive updated August 05, 2010 @ 11:25PM EST)
