• Tweet
• Buffer
•  30

• Tweet
• Buffer

If you have a website, you are probably interested in knowing who visits you site. You might want to know the number of visitors, where are they from, how did they get to your site, etc. There are some online tracking options available, like google analytics or if you are using a hosting service they might offer you some statistics. But what if you want a personalized tracker that shows you only what you need? In this tutorial I will show you how to build one!

What are we going to build today?

I’ll show you how to build a small tracker php script that saves the following information about your visitors in a database: their ip address, their location (country and city) based on their ip, the date and time of their visit, some information about the browser they used and their operating system, the referer (if they clicked on a link on another site to get to yours, you will know which site referred them) and the query string they searched for in case they were referred by a search engine. I will also show you how to check if the visitor was a bot (these are software applications that run automated tasks over the Internet). I’ll show you how to write this small script and also a page with some statistics from the database.

You can download the source code here. You can also see a live demonstration here.

First, we need a table in the database to hold the information about the visitors. We’ll call it “tracker”.

We’ll need the following columns in the table: id, date, time, ip, country, city, query_string, http_referer, http_user_agent, isbot (this will hold the value 1 is the user is a bot and 0 otherwise).

Here’s the sql command needed to create the table:

CREATE TABLE IF NOT EXISTS `tracker` (
`id` int(11) NOT NULL auto_increment,
`date` date NOT NULL,
`time` time NOT NULL,
`ip` text NOT NULL,
`country` text NOT NULL,
`city` text NOT NULL,
`query_string` text NOT NULL,
`http_referer` text NOT NULL,
`http_user_agent` text NOT NULL,
`isbot` int(11) NOT NULL,
PRIMARY KEY  (`id`)
);

Next, we’ll need a small php script that finds out all the needed info and inserts it into the database.

Here are the things we will need to do:

• Get the necessary info from the server
• Find the location based on the ip address
• Check if the visitor is a bot
• Insert everything into the database

Here’s the code to retrieve the information from the server:

$ip = $_SERVER['REMOTE_ADDR'];
$query_string = $_SERVER['QUERY_STRING'];
$http_referer = $_SERVER['HTTP_REFERER'];
$http_user_agent = $_SERVER['HTTP_USER_AGENT'];

$_SERVER is an array containing information such as headers, paths, and script locations. The entries in this array are created by the web server. We will save the ip of the visitor ($_SERVER['REMOTE_ADDR']), the referrer ($_SERVER['HTTP_REFERER']), the query string user to search ($_SERVER['QUERY_STRING']) and the data about the visitors browser and operating system (SERVER['HTTP_USER_AGENT']).

The function to check if a visitor is a bot looks like this:

function is_bot()
{
$botlist = array("Teoma", "alexa", "froogle", "Gigabot", "inktomi",
"looksmart", "URL_Spider_SQL", "Firefly", "NationalDirectory",
"Ask Jeeves", "TECNOSEEK", "InfoSeek", "WebFindBot", "girafabot",
"crawler", "www.galaxy.com", "Googlebot", "Scooter", "Slurp",
"msnbot", "appie", "FAST", "WebBug", "Spade", "ZyBorg", "rabaz",
"Baiduspider", "Feedfetcher-Google", "TechnoratiSnoop", "Rankivabot",
"Mediapartners-Google", "Sogou web spider", "WebAlta Crawler","TweetmemeBot",
"Butterfly","Twitturls","Me.dium","Twiceler");
foreach($botlist as $bot)
{
if(strpos($_SERVER['HTTP_USER_AGENT'], $bot) !== false)
return true;
}
return false;
}

I have found a list of bots. To check is a visitor is a bot, we need to check the $_SERVER['HTTP_USER_AGENT'] variable.

Finding out the location details using the ip address is a bit more difficult. We will need to make a request to a server that finds this sort of information, get the result from the server (it sends an xml file), parse the result and get the country and city names.

The php code looks like this:

$url = 'http://ipinfodb.com/ip_query.php?ip='.$ip;
$init = curl_init($url);
curl_setopt($init, CURLOPT_RETURNTRANSFER, true);
curl_setopt($init, CURLOPT_TIMEOUT, 10);
$exec = curl_exec($init);
$info = curl_getinfo($init);
curl_close($init);
if($info['http_code'] === 200)
{
// parse the xml and get country and city from exec
$objDOM = new DOMDocument();
$objDOM->loadXML($exec);

$country1 = $objDOM->getElementsByTagName("CountryName");
$country  = $country1->item(0)->nodeValue;
$city1 = $objDOM->getElementsByTagName("City");
$city  = $city1->item(0)->nodeValue;
}

We first define the url of the server (http://ipinfodb.com/ip_query.php) and send a request to it with the visitors ip address. We’re going to use curl to send the request to the server and retrieve the result. curl is a tool for transferring data using various protocols. We first initialize the session, set a few parameters and then send the request. The result will be saved in the $exec variable. The result from the server is an xml file. We need to parse it and get two values, country and city. There values are found in the “CountryName” and “City” tags. We’re going to use the DOMDocument class to parse the xml file.

We set the $isbot variable to 1 if the visitor is a bot and 0 otherwise. We will save this variable in the database.

if (is_bot())
$isbot = 1;
else
$isbot = 0;

We’ll get the system date and time

$date = date("Y-m-d");
$time = date("H:i:s");

We’ve retrieved all the information we wanted! Next, we will have to add all the information into the database.

We need to set the database server, database name, user name and password, then connect to the database server and the database.

$server = "SERVER NAME";
$username = "USER NAME";
$password = "PASSWORD";
$database = "DATABASE NAME";
$connId = mysql_connect($server,$username,$password) or die("Cannot connect to server");
$selectDb = mysql_select_db($database,$connId) or die("Cannot connect to database");

Then, we run the query to insert the information into the tracker table.

$query = "insert into `tracker` (`country`,`city`,`date`, `time`, `ip`, `query_string`, `http_referer`, `http_user_agent`, `isbot`)
values ('$country','$city','$date', '$time', '$ip', '$query_string', '$http_referer' ,'$http_user_agent' , $isbot)";
$result = mysql_query($query);

All the information we wanted is now saved in the database!

All we need to do now is include this script in the main page of the site and we can track the visitors! We could also add this script to the other pages of the site, add another variable to hold the page name and insert this in the database as well. This way, we will know exactly what pages did the user browse on the site.

I’ll also show you how to create a page to view some statistics. There are loads of statistics that you may want to view using the information from the database. I’ll show you a small example, which will do the following:

• show the number of unique visitors
• show a table with all the visitors

The code for this is shown below.

<html>

<head>
	<title>Statistics</title>
</head>

<body>

	<h1>Statistics</h1>

	<br/><br/>

	The number of unique visitors is:
	<?php
	// connect to the database

	// fill in your databasa data here!
	$server = "SERVER NAME";
	$username = "USER NAME";
	$password = "PASSWORD";
	$database = "DATABASE NAME";

	$connId = mysql_connect($server,$username,$password) or die("Cannot connect to server");
	$selectDb = mysql_select_db($database,$connId) or die("Cannot connect to database");

	// get the number of unique visitors
	$query = "select distinct ip from tracker";
	$result = mysql_query($query);
	$number = mysql_num_rows($result);

	// show the number
	echo $number;
	?>

	<br/><br/>

	Site's visitors:
	<br/><br/>
	<table border="1">
		<tr>
			<th>Id</th>
			<th>IP</th>
			<th>Country</th>
			<th>City</th>
			<th>Referer</th>
			<th>Is a bot?</th>
		</tr>
		<?php
		// get the list of visitors
		$query = "select * from tracker";
		$result = mysql_query($query);
		while ($row = mysql_fetch_array($result))
		{
		?>
			<tr>
				<td><?php echo $row['id'];?></td>
				<td><?php echo $row['ip'];?></td>
				<td><?php echo $row['country'];?></td>
				<td><?php echo $row['city'];?></td>
				<td><?php echo $row['http_referer'];?></td>
				<td><?php if ($row['isbot']==1) echo "yes"; else echo "no";?></td>
			</tr>
		<?php
		}
		?>
	</table>

</body>

</html>

What we’ve done was to retrieve the information from the database using the appropriate sql queries and print the information on the screen.

And that’s about it! We now have a visitor tracker!

If you have questions, comments or need further details on something, please let me know. I’d be happy to help!