PHP & MySQL Visitors Counter Tutorial

We'll start with creating the database for the script, or actually the mysql table that can be put in any database you desire (for ex. the database of your website). Each visit will be logged inside of this database table. The info that will be logged is: the location of the visit (the file path) and the IP of the visitor. Also each log in this database table will have its own unique number (id).

 

id

location

ip

which is in SQL code:

CREATE TABLE IF NOT EXISTS `counter` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`location` varchar(250) NOT NULL,
`ip` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now it's time to create the actual script. When using this script, do make sure you have made a connection to your MySQL database in which you put the MySQL table ('counter'). For example:

mysql_connect("localhost", "root", "password");// server, user, pass
mysql_select_db("webcodez"); // database

but then of course use your MySQL connection info.

We'll start with creating the first function of the script: initCounter. This function needs to make a log of the visit inside of the database table. It needs the ip of the visitor and the location of the file to do this. To acquire these informations we can use the global array $_SERVER, which contains all kinds of useful data ( including the visitor's ip and server file path ).

function initCounter() {

$ip = $_SERVER['REMOTE_ADDR']; //get visitor ip
$location = $_SERVER['PHP_SELF']; //get server file path

//create log in database table 'counter'

}

Now the function needs to make a log of this visit inside of the database table 'counter' ( which we created in the previous post ). This can be done by just running a simple MySQL query:

function initCounter() {

$ip = $_SERVER['REMOTE_ADDR']; //get visitor ip
$location = $_SERVER['PHP_SELF']; //get server file path

//create log in database table 'counter'
$create_log = mysql_query("INSERT INTO counter(ip,location)VALUES('$ip', '$location') ");

}

Alright, time to create the second and last function of the script: getCounter. This function needs to return the amount of unique visits OR the amount of total hits of the current page, depending on the value given to the argument $mode of the function upon calling it. We make 2 different MySQL queries to do this ( for both cases 1 ).

function getCounter($mode) {

if($mode == "unique") {
$get_res = mysql_query("SELECT DISTINCT ip FROM counter WHERE location = '".$_SERVER['PHP_SELF']."' ");
}else{
$get_res = mysql_query("SELECT ip FROM counter WHERE location = '".$_SERVER['PHP_SELF']."' ");
}

}

The first query selects all UNIQUE (DISTINCT) ip addresses. Or in other words: all unique visits. The second query selects just ALL visits, and counts visits from the same ip as different ones (so it gets all hits). Now we do need to count the actual amount of results (visits) found by the query. We'll use the function mysql_num_rows to do this:

function getCounter($mode) {

if($mode == "unique") {
$get_res = mysql_query("SELECT DISTINCT ip FROM counter WHERE location = '".$_SERVER['PHP_SELF']."' ");
}else{
$get_res = mysql_query("SELECT ip FROM counter WHERE location = '".$_SERVER['PHP_SELF']."' ");
}

$res = mysql_num_rows($get_res);

return $res;

}

There's one more thing we could add to this function: the ability to display the amount of visits of another page ( other than the current page ). This is fairly easy as we can just add another argument to the function for the location of the file.

function getCounter($mode, $location = NULL)

we give it as default value NULL and give it the value of the the location of the current page if no other value is given. This way, when calling the function, it's not necessary to provide the location of the page but it IS possible. What we do need to do now, is replacing $_SERVER['PHP_SELF'] by $location inside of the function script. This way it will use the newly created argument $location, so that the user of this function can also provide a different location rather than the current page.

function getCounter($mode, $location = NULL) {

if(is_null($location)) {
$location = $_SERVER['PHP_SELF'];
}

if($mode == "unique") {
$get_res = mysql_query("SELECT DISTINCT ip FROM counter WHERE location = '$location' ");
}else{
$get_res = mysql_query("SELECT ip FROM counter WHERE location = '$location' ");
}

$res = mysql_num_rows($get_res);

return $res;

}

We put both functions together and we have our visitor counter script!

counter.php

<?php

function initCounter() {

$ip = $_SERVER['REMOTE_ADDR']; //get visitor ip
$location = $_SERVER['PHP_SELF']; //get server file path

//create log in database table 'counter'
$create_log = mysql_query("INSERT INTO counter(ip,location)VALUES('$ip', '$location') ");

}

function getCounter($mode, $location = NULL) {

if(is_null($location)) {
$location = $_SERVER['PHP_SELF'];
}

if($mode == "unique") {
$get_res = mysql_query("SELECT DISTINCT ip FROM counter WHERE location = '$location' ");
}else{
$get_res = mysql_query("SELECT ip FROM counter WHERE location = '$location' ");
}

$res = mysql_num_rows($get_res);

return $res;

}

?>

Update: Instead of using mysql_num_rows to count the amount of results, we could also do this within the query (=faster):

function getCounter($mode, $location = NULL) {

if(is_null($location)) {
$location = $_SERVER['PHP_SELF'];
}

if($mode == "unique") {
$get_res = mysql_query("SELECT COUNT(DISTINCT ip) AS amt FROM counter WHERE location = '$location' ");
}else{
$get_res = mysql_query("SELECT COUNT(ip) AS amt FROM counter WHERE location = '$location' ");
}

$res = mysql_fetch_assoc($get_res);

return $res['amt'];

}

We can use our visitor counter script in any webpage we like. All we have to do is include the script and run both functions: initCounter to make a log of each visit and getCounter to get the total amount of visits. For example:

<?php

//usage of the script in an example webpage

include_once("counter.php"); //do include the actual visitor counter script

initCounter(); //make a log of the current visit

echo "Hi there, this is an example web page. <p>";

echo "This webpage has got a total of ".getCounter('hits')." hits from which ".getCounter('unique')." unique.";

?>