How to do a simple tracking script for your links/ads in PHP

Have you ever wanted to track how many people clicked an ad/image/link on your web page?

If yes then sit down and read this tutorial 🙂

So let’s start …

What do we need? A script that will take as input an URL, increase its counter and then redirect instantly the user to the tracked URL.

First of all we need a MySQL table to store the data :

CREATE TABLE `tracking` (
  `id` int(11) NOT NULL auto_increment,
  `url` text NOT NULL,
  `ip` varchar(15) NOT NULL default '0.0.0.0',
  `date` int(11) NOT NULL default '0',
  `referer` text NOT NULL,
  PRIMARY KEY  (`id`)
);

Let’s describe a little all the fields of the ‘tracking’ table :

  • ‘id’ – used for having an index
  • ‘url’ – tracked url
  • ‘ip’ – the IP from which the click came
  • ‘date’ – time & date of the click (it is stored in unixtime)
  • ‘referer’ = the referer (in our case it is the page which contains the tracking link

We must now think what kind of functions we’ll need …
Functions for :

  • inserting a track
  • clearing a track/all tracks
  • retrieving tracks
  • doing the redirect
  • top50 tracks/referers

Done planning, let’s start the real work 😛 :

db = new mysql_dialog(true);
	$this->db->connect($dbserver, $dbuser, $dbpass, $dbname);
 }

 //insert a track
 function InsertTrack($url)
 {
  	//get the visitor's IP
  	$ip = $_SERVER['REMOTE_ADDR'];
  	//get the refering page
  	$ref = $_SERVER["HTTP_REFERER"];
  	$url = addslashes($url);
	//build and execute the SQL query
	$sql = "INSERT INTO tracking VALUES('0','$url','$last_ip','" . time() . "','$ref')";
	$this->db->speak($sql);
 }

 //redirect the user to the target page
 function ContinueToPage($url)
 {
    $htmlCode = "";
    $htmlCode .= "<__meta http-equiv='refresh' content=0;url='" . $sURL . "'>";
    $htmlCode .= "";
    print $htmlCode;
 }

 //retrieve the last 50 tracks (for the statistics panel)
 function GetLast50Tracks()
 {
  	//query the DB for the last 50 tracks
	$sql_code = "SELECT * FROM tracking ORDER BY date DESC LIMIT 50;";
	$this->db->speak($sql_code);
	//store all the data in an array
	$v = "";
	while ($data = $this->db->listen()) 
	{
		$v[]=$data;
	}	
	return $v;
 }

 //retrieve the top 50 tracks (for the statistics panel)
 function GetTop50Tracks()
 {
	//query the DB for the top 50 tracks
	//cnt will store the number of hits per tracked url
	$sql_code = "SELECT count(*) AS cnt,url,ref from tracking group by url order by cnt DESC LIMIT 50;";
	$this->db->speak($sql_code);
	//store all the data in an array
	$v = "";
	while ($data = $this->db->listen()) 
	{
		$v[]=$data;
	}	
	return $v;
 }

 //retrieve the top 50 referers (for the statistics panel)
 function GetTop50Referers()
 {
	//query the DB for the top 50 referers
	//cnt will store the number of hits per tracked url
	$sql_code = "SELECT count(*) AS cnt,url,ref from tracking group by kid order by cnt DESC LIMIT 50;";
	$this->db->speak($sql_code);
	//store all the data in an array
	$v = "";
	while ($data = $this->db->listen()) 
	{
		$v[]=$data;
	}	
	return $v;
 }

 //clear a specific track or all tracks
 function ClearTracks($track_id,$flush=false)
 {
  	//delete all or a single specific track
  	if ($flush==true)
  	{
  	 $sql_code = "DELETE FROM tracking;";
  	}
    else
    {
     $sql_code = "DELETE FROM tracking WHERE id='$track_id';";
    }
    //execute the query
   	$this->db->speak($sql_code);
 }

 //retrieve all the tracks made between date d1 and date d2 (for the statistics panel)
 function GetSearchResults($d1,$d2)
 {
  	//check dates
  	if ($d1>$d2)
  	{
  	 $aux=$d1;
  	 $d1=$d2;
  	 $d2=$aux;
  	}
  	//query the DB for the tracks between d1 and d2
	$sql_code = "SELECT * FROM tracking WHERE date>$d1 AND date<$d2  ORDER BY date DESC;";
	$this->db->speak($sql_code);
	//store all the data in an array
	$v = "";
	while ($data = $this->db->listen()) 
	{
		$v[]=$data;
	}	
	return $v;
 }

};

?>

* on the line 31 replace “__meta” with “meta”, I changed this because there is a bug in the wordpress plugin I use for the syntax highlighting.

Copy and paste all the code above into a new file called “class.track.php5” and we are done with the tracking code 🙂
As you can see on the class constructor we use for the MySQL queries another class named “mysql_dialog”, it was written by Giorgos Tsiledakis and I use it for a very long time (I’m too lazzy to write my own mysql dialog class). Copy & Paste it from here.

Now let’s crop a simple example for our class …

ContinueToPage("http://stuff.nekhbet.ro");

//decode our tracked url
$url = base64_decode(trim($_REQUEST["url"]));

//insert the track into the DB
$track->InsertTrack("$url");
//redirect the user to the tracked url
$track->ContinueToPage("$url");
?> 

Save that file as “track.php”.

Now for usage, in the page you have a link you want to track rewrite it as below :

...
$link = "/path/to/track.php?url=" . base64_encode("$your_link_to_track");
...

Instead of using Base64 you can also use the PHP functions : urlencode() and urldecode().

If you have questions please ask 🙂

Hope this helps

3 comments on “How to do a simple tracking script for your links/ads in PHP

  1. Hi i tried your tutorial but i got the following errors

    Undefined index: HTTP_REFERER in C:\xampp\htdocs\tracking\class.track.php5 on line 22

    Undefined variable: last_ip in C:\xampp\htdocs\tracking\class.track.php5 on line 26

    Undefined variable: sURL in C:\xampp\htdocs\tracking\class.track.php5 on line 34

    Please let me know of fixes so i can apply it

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.