Skip to content

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 :

SQL [Show Plain Code]:
  1. CREATE TABLE `tracking` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `url` text NOT NULL,
  4.   `ip` varchar(15) NOT NULL DEFAULT ’0.0.0.0′,
  5.   `date` int(11) NOT NULL DEFAULT ’0′,
  6.   `referer` text NOT NULL,
  7.   PRIMARY KEY  (`id`)
  8. );

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

PHP [Show Plain Code]:
  1. <?php
  2.  
  3. include_once("class.mysql.php5");
  4.  
  5. class Tracking
  6. {
  7.  
  8.  //the class constructor, you need PHP5 for this
  9.  function __construct()
  10.  {
  11.         //we have those variables in a config.php file
  12.         global $dbserver, $dbuser, $dbpass, $dbname;
  13.         //connect to the DB
  14.         $this->db = new mysql_dialog(true);
  15.         $this->db->connect($dbserver, $dbuser, $dbpass, $dbname);
  16.  }
  17.  
  18.  //insert a track
  19.  function InsertTrack($url)
  20.  {
  21.         //get the visitor’s IP
  22.         $ip = $_SERVER[‘REMOTE_ADDR’];
  23.         //get the refering page
  24.         $ref = $_SERVER["HTTP_REFERER"];
  25.         $url = addslashes($url);
  26.         //build and execute the SQL query
  27.         $sql = "INSERT INTO tracking VALUES(’0′,’$url’,'$last_ip’,'" . time() . "’,'$ref’)";
  28.         $this->db->speak($sql);
  29.  }
  30.  
  31.  //redirect the user to the target page
  32.  function ContinueToPage($url)
  33.  {
  34.     $htmlCode = "<html><head><title></title>";
  35.     $htmlCode .= "<__meta http-equiv=’refresh’ content=0;url=’" . $sURL . "’>";
  36.     $htmlCode .= "</head><body></body></html>";
  37.     print $htmlCode;
  38.  }
  39.  
  40.  //retrieve the last 50 tracks (for the statistics panel)
  41.  function GetLast50Tracks()
  42.  {
  43.         //query the DB for the last 50 tracks
  44.         $sql_code = "SELECT * FROM tracking ORDER BY date DESC LIMIT 50;";
  45.         $this->db->speak($sql_code);
  46.         //store all the data in an array
  47.         $v = "";
  48.         while ($data = $this->db->listen())
  49.         {
  50.                 $v[]=$data;
  51.         }       
  52.         return $v;
  53.  }
  54.  
  55.  //retrieve the top 50 tracks (for the statistics panel)
  56.  function GetTop50Tracks()
  57.  {
  58.         //query the DB for the top 50 tracks
  59.         //cnt will store the number of hits per tracked url
  60.         $sql_code = "SELECT count(*) AS cnt,url,ref from tracking group by url order by cnt DESC LIMIT 50;";
  61.         $this->db->speak($sql_code);
  62.         //store all the data in an array
  63.         $v = "";
  64.         while ($data = $this->db->listen())
  65.         {
  66.                 $v[]=$data;
  67.         }       
  68.         return $v;
  69.  }
  70.  
  71.  //retrieve the top 50 referers (for the statistics panel)
  72.  function GetTop50Referers()
  73.  {
  74.         //query the DB for the top 50 referers
  75.         //cnt will store the number of hits per tracked url
  76.         $sql_code = "SELECT count(*) AS cnt,url,ref from tracking group by kid order by cnt DESC LIMIT 50;";
  77.         $this->db->speak($sql_code);
  78.         //store all the data in an array
  79.         $v = "";
  80.         while ($data = $this->db->listen())
  81.         {
  82.                 $v[]=$data;
  83.         }       
  84.         return $v;
  85.  }
  86.  
  87.  //clear a specific track or all tracks
  88.  function ClearTracks($track_id,$flush=false)
  89.  {
  90.         //delete all or a single specific track
  91.         if ($flush==true)
  92.         {
  93.          $sql_code = "DELETE FROM tracking;";
  94.         }
  95.     else
  96.     {
  97.      $sql_code = "DELETE FROM tracking WHERE id=’$track_id’;";
  98.     }
  99.     //execute the query
  100.         $this->db->speak($sql_code);
  101.  }
  102.  
  103.  //retrieve all the tracks made between date d1 and date d2 (for the statistics panel)
  104.  function GetSearchResults($d1,$d2)
  105.  {
  106.         //check dates
  107.         if ($d1>$d2)
  108.         {
  109.          $aux=$d1;
  110.          $d1=$d2;
  111.          $d2=$aux;
  112.         }
  113.         //query the DB for the tracks between d1 and d2
  114.         $sql_code = "SELECT * FROM tracking WHERE date>$d1 AND date<$d2  ORDER BY date DESC;";
  115.         $this->db->speak($sql_code);
  116.         //store all the data in an array
  117.         $v = "";
  118.         while ($data = $this->db->listen())
  119.         {
  120.                 $v[]=$data;
  121.         }       
  122.         return $v;
  123.  }
  124.  
  125. };
  126.  
  127. ?>

* 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 …

PHP [Show Plain Code]:
  1. <?php
  2. //include our class
  3. include ("class.track.php5");
  4.  
  5. //make an instance of our class
  6. $track = new Tracking();
  7.  
  8. //try to see if we have an url parameter
  9. if (!$_REQUEST["url"])
  10.         //if not just redirect the user to our main page
  11.         $track->ContinueToPage("http://stuff.nekhbet.ro");
  12.  
  13. //decode our tracked url
  14. $url = base64_decode(trim($_REQUEST["url"]));
  15.  
  16. //insert the track into the DB
  17. $track->InsertTrack("$url");
  18. //redirect the user to the tracked url
  19. $track->ContinueToPage("$url");
  20. ?>

Save that file as “track.php”.

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

PHP [Show Plain Code]:
  1. …
  2. $link = "/path/to/track.php?url=" . base64_encode("$your_link_to_track");
  3. …

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

If you have questions please ask :)

Hope this helps

Posted in PHP Tips & Tricks.

2 comments

By Trimbitas Sorin – February 3, 2007

2 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Url Redirection says

    Hello, Nice blog posting about . I would have to agree with you on this one. I am going to look more into url redirection. This Saturday I have time.

    September 15, 2007, 8:34 pm
  2. Aka the Tracker (Akram Hindi) says

    finally feel found a website on my wavelength.
    this is good stuff really good!
    Thanks Trimbitas

    December 2, 2008, 9:40 am



Some HTML is OK

or, reply to this post via trackback.

« »
Get Adobe Flash playerPlugin by wpburn.com wordpress themes