How to Build a Live Visitor Tracking System for your Website

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?

Article updated: April 23, 2013

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, parse the result and get the country and city names. We will be using the API from ipinfodb.com. To be able to use their service, you will need to request a free API key. You can find more info about how to get one and how to use their service on their site.

The php code we need looks like this:

include('ip2locationlite.class.php');
//Load the class
$ipLite = new ip2location_lite;
$ipLite->setKey('ADD_API_KEY_HERE');

//Get errors and locations
$locations = $ipLite->getCity($ip);
$errors = $ipLite->getError();

//Getting the result
if (!empty($locations) && is_array($locations)) {
  foreach ($locations as $field => $val) {
  	if ($field == 'countryName')
  		$country = $val;
    if ($field == 'cityName')
  		$city = $val;
  }
}

The php class is provided by ipinfodb to make it easier for us to use the service. Make sure to set the API key in the code. All this code does is to call the getCity() method from their API to get the location info and check in the result for the values we want (city and country names).

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");

Another thing we have to be careful about is preventing any sql injection attacks. For this, we will have to apply the method mysql_real_escape_string() to all values before inserting them into the 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><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>

Irina Borozan

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.

15 Smart Tools To Help You Build Your Freelance Business

Discover the awesome tools we use in making our clients comfortable and happy in learning new things every day.

Download Now

Comments

  1. kenny says

    i just tried out your code, great work!!! but the country and city are coming out blank, any solutions or update ??

  2. Aiaa says

    Hi every one my name is AlAa

    My problem with $_SERVER[‘remote_addr’]

    I’m not sure if I’m having a problem with the installation of PHP on the server or if there’s something that I’m not doing right in the code, but I am trying to capture the IP address of anyone that is trying to log into a secure part of a site. Regardless of my IP address, when I do:

    Code:

    echo $_SERVER[‘remote_addr’];

    It returns ‘192.168.1.1’. This is the first time I’ve written code that is being used on a Windows Server, so I don’t know if that has anything to do with it.

    I’ve run phpinfo(), and in the PHP Variables section, it shows “_SERVER[“REMOTE_ADDR”]” : 192.168.1.1. Does this mean that the php.ini file is setup wrong, or am I missing something?

    i am running my own server on windows 7 iis7.5 with php as fastcgi and i am behind a router with a dynamic ip not static and i am using
    http://www.no-ip.com/ that give me a link to my router and i am opening 80 port in my router forwarding it to my id address 192.168.1.5 and i have tested a php file that contains

    tested it using tor network that give me ip out of my network to test that file always give me the same result 192.168.1.1 i am trying to get IP address of the visitor and i asked someone of my friends to use this form it sends the same ip

    i hope someone tells me what i am doing wrong and the solution for this problem

    Thanks in advance

    • says

      ipinfodb has been change their query and api, if you still want use the same code as tutorial you will need commercial account.

  3. xsilent says

    how to make the link of visitor become hyperlink, so it url could be click and go to visitor web.?

  4. Shawn says

    Can I add visitors tracking code to a specific google search? If so, would you please let me know.
    Thanks

  5. Vincent says

    With real-time in mind, I created Voxmap as a side-project ). 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!!!! :)

  6. Tito says

    this script doesnt work as of now. the ipndb web address has changed and xml parsing fails.

  7. Janis Alnis says

    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.

  8. BillyFielder says

    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.

  9. Birju says

    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

  10. Ao4343 says

    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

  11. Ilie Ciorba says

    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.

  12. says

    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 :)

  13. Matthew says

    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.

  14. Jani Peltoniemi says

    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 :)

  15. says

    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.