How to Build a Live Visitor Tracking System for your Website

Posted in Tutorials 715 days ago • Written by:

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-&gt;loadXML($exec);

$country1 = $objDOM-&gt;getElementsByTagName("CountryName");
$country  = $country1-&gt;item(0)-&gt;nodeValue;
$city1 = $objDOM-&gt;getElementsByTagName("City");
$city  = $city1-&gt;item(0)-&gt;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!

<html>

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

<body>

<h1>Statistics</h1>

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;
?>

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>

For Further Reading:

How To Grow as a Designer by Building Your Own Network

What is Pro Bono Work and Why Should You Embrace It

How to Overcome the Bad Moments of Your Career Like a Boss

First Steps in Creating a Web Design Business

Did you enjoy this article and found it useful?

Get even more from us:

Follow @1stwebdesigner

Get Our RSS Feed

Article was created by Irina Borozan

Articles

Visit Irina Borozan's Website | Follow Irina Borozan On Twitter

Irina is a software developer from Bucharest, Romania. She enjoys working on various programming projects. She likes spending her spare time reading, hiking, travelling, skating or playing tennis. She also blogs a bit. You can find her online in her home on the internet and on twitter.

39 Brilliant Comments - Join Discussion Now!

avrsastry
Posted 182 days ago
29

thanks
Serendipity
Posted 212 days ago
28

Can I add visitors tracking code to a specific google search? If so, would you please let me know.
Thanks
Vincent
Posted 248 days ago
27

With real-time in mind, I created Voxmap as a side-project (http://voxmap.com). It’s a very very cool way to visualize your traffic (basically simple analytics + Google Earth API). It’s free for everyone!

It only works with the latest FF or Chrome. Feel free to send me any feedback!!!! :)
Tito
Posted 258 days ago
26

this script doesnt work as of now. the ipndb web address has changed and xml parsing fails.
Janis Alnis
Posted 264 days ago
25

Thanks for nice tutorial.
It was my first time to use sql. I have a Debian server. It took some time to find out how to get started.
————————————————————–
apt-get install php5-curl php5-mysql
apt-get install mysql-server
————————————————————–
Login and create database with a table
mysqladmin -u root -p
create database tracker;
use tracker;
Now paste in the file contents of tracker.sql within the [sgl] brackets

————————————————————-
In php files change
// fill in your database data here!
$server = “localhost”;
$username = “root”;
$password = “abcd”;
$database = “tracker”;

Now go to the webpage and type tracker.php webaddress and it should execute without errors. And then got to
stats.php that should show a table.
DAVYMLLC
Posted 266 days ago
24

I am so lost on this. Love the idea of this code but need help implementing it.
Michael
Posted 266 days ago
23

Very nice, and very useful! Right to the meat of it. Thanks!
Appy
Posted 272 days ago
22

Can it be converted to another language like VB.Net or C#.Net
BillyFielder
Posted 309 days ago
20

i get errors with the & symbols in the: $objDOM- & gt;loadXML($exec);
says its unexpected :(

well personally i think the computer should expect the unexpected.
Zsolt
Posted 344 days ago
19

Nice tutorial,
Thanks

Irina
Posted 274 days ago
21

I’m glad you liked it!

Zsolt
Posted 344 days ago
18

Nice tutorial, thanks!
Birju
Posted 547 days ago
17

hi its my mazor project in the clg so i want initial steps for making sites in WPF fondation……plz snd more tutorial about wek tracker
Ao4343
Posted 556 days ago
16

Hi, I been using your code to get locations but now its not working and I noticed the ipinfodb.com has changed their php file to ip_locator.php but when I changed mine, i get tons of dom errors, what am I doing wrong? Thanks
max
Posted 628 days ago
14

How do i put this script on my site…??????plz help

Irina Borozan
Posted 619 days ago
15

You can download the source code and personalize it. You will just need to include the tracker script in your source code.

Ilie Ciorba
Posted 710 days ago
13

Interesting tut, mostly I don’t need a live tracking system for my blog, but from time to time I really want to know how many peoples are currently reading it, or how many alredy visited today.
.-= Ilie Ciorba´s last blog ..Integration between Illustrator and Flash Catalyst CS5 =-.
Rohan
Posted 712 days ago
12

Nice Code.
Thanks,
(3cci.in)
Irina Borozan
Posted 715 days ago
10

Sorry for the weird looking code. I just noticed it, I’ll try and fix it. It’s not a bug though, it just shows the character codes for some characters instead of the actual character.

@Matt Pritchett: yes, it is usable in wordpress, you just have to add it to your worpress code.

@Matthew: I’ve not tested it on a high traffic site, but on a small traffic site it works ok :)
Matthew
Posted 715 days ago
9

This is a bad idea. why ? MySQL is not the correct tool to use for this level of logging. If it was this simple then everyone would be doing this. they dont.

The best way to do this is at server log level if possible. Use a database like couchDB or MongoDB to store the data.

eg: write a hook for your Apache vonfig to store the data in the mongo DB using python.

Even if you cant do this, you would want to at the very least not use mysql queries from PHP to inject the data. why ? it will get slower the bigger the table gets.

File based dbs can handle these huge amounts of data with fast write access. The above code would crash your server if you put any large load on it.

Its a good concept and a great beginner tutorial but I would not put it into production on anything except a low traffic hobby site.
Monie
Posted 715 days ago
7

Something wrong with the code view above? Perhaps a bug or something?

Dainis Graveris
Posted 715 days ago
8

what is wrong with it? can you give a screenshot? looks fine in my computer!
Saad Bassi
Posted 714 days ago
11

Just fixed it Monic. That weird because is because of WordPress auto formatting behaviour. Sorry for inconvenience.

Irina Borozan
Posted 715 days ago
6

Thanks for the comments!

@Aka: thanks for pointing that out! I’ll have to look more into this.
Matt Pritchett
Posted 715 days ago
5

This is great! But would it be usable in WordPress?
.-= Matt Pritchett´s last blog ..How I Deal With Terrible Clients =-.
Jani Peltoniemi
Posted 715 days ago
4

not to sound too picky, but I think this
if (is_bot())
$isbot = 1;
else
$isbot = 0;

should be like this:
$isbot=is_bot();

Also, I’d advise using real boolean values instead of 1 and 0. It’s not a big deal but it’s a nice convention.

You probably should also fix the entities in the bottommost code block. Makes it a tad easier to read :)
Kent Design
Posted 715 days ago
3

Great post, food for thought… it might well tackle this!
Waheed Akhtar
Posted 715 days ago
2

detailed tutorial. I like it
.-= Waheed Akhtar´s last blog ..Super Macro Photos Of Ladybugs =-.
Aka
Posted 715 days ago
1

Nice post but you have some SQL Injection issues.
For example if i fake my user agent with quote in it your query will break.
Same thing for HTTP_REFERER. We trust server variable way too much.

In the same way, we can exploit the line 59 (<td><?php echo $row['http_referer'];?></td>) via an XSS. We can put html into our referer and bam, broken.
.-= Aka´s last blog ..akashrine: Selection de webdesign épuré #minimalism http://icio.us/t5wkc3 =-.

1 2

How to Build a Live Visitor Tracking System for your Website

What are we going to build today?

For Further Reading:

Did you enjoy this article and found it useful?

Article was created by Irina Borozan

39 Brilliant Comments - Join Discussion Now!

avrsastry

Serendipity

Vincent

Tito

Janis Alnis

DAVYMLLC

Michael

Appy

BillyFielder

Zsolt

Irina

Zsolt

Birju

Ao4343

max

Irina Borozan

Ilie Ciorba

Rohan

Irina Borozan

Matthew

Monie

Dainis Graveris

Saad Bassi

Irina Borozan

Matt Pritchett

Jani Peltoniemi

Kent Design

Waheed Akhtar

Aka

Join Our Email Newsletter

Pages

Categories