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 :

  1. span style=”color: #ff0000;”>`tracking` (
  2.   `id``url``ip`‘0.0.0.0’,
  3.   `date`‘0’,
  4.   `referer``id`)
  5. );

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

  1. span style=”color: #ff0000;”>"class.mysql.php5"//the class constructor, you need PHP5 for this
  2. //we have those variables in a config.php file
  3. //connect to the DB
  4. //insert a track
  5. //get the visitor’s IP
  6. ‘REMOTE_ADDR’];
  7.         //get the refering page
  8. "HTTP_REFERER"//build and execute the SQL query
  9.         $sql = "INSERT INTO tracking VALUES(‘0′,’$url’,’$last_ip’,’""’,’$ref’)";
  10.         $this->db->speak($sql);
  11.  }
  12.  
  13.  //redirect the user to the target page
  14. "<html><head><title></title>";
  15.     $htmlCode .= "<__meta http-equiv=’refresh’ content=0;url=’" . $sURL . "’>";
  16.     $htmlCode .= "</head><body></body></html>"//retrieve the last 50 tracks (for the statistics panel)
  17. //query the DB for the last 50 tracks
  18. "SELECT * FROM tracking ORDER BY date DESC LIMIT 50;"//store all the data in an array
  19.         $v = ""//retrieve the top 50 tracks (for the statistics panel)
  20. //query the DB for the top 50 tracks
  21.         //cnt will store the number of hits per tracked url
  22. "SELECT count(*) AS cnt,url,ref from tracking group by url order by cnt DESC LIMIT 50;"//store all the data in an array
  23.         $v = ""//retrieve the top 50 referers (for the statistics panel)
  24. //query the DB for the top 50 referers
  25.         //cnt will store the number of hits per tracked url
  26. "SELECT count(*) AS cnt,url,ref from tracking group by kid order by cnt DESC LIMIT 50;"//store all the data in an array
  27.         $v = ""//clear a specific track or all tracks
  28. //delete all or a single specific track
  29. "DELETE FROM tracking;""DELETE FROM tracking WHERE id=’$track_id’;";
  30.     }
  31.     //execute the query
  32. //retrieve all the tracks made between date d1 and date d2 (for the statistics panel)
  33. //check dates
  34. //query the DB for the tracks between d1 and d2
  35. "SELECT * FROM tracking WHERE date>$d1 AND date<$d2  ORDER BY date DESC;"//store all the data in an array
  36.         $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 …

  1. span style=”color: #808080; font-style: italic;”>//include our class
  2. "class.track.php5");
  3.  
  4. //make an instance of our class
  5. //try to see if we have an url parameter
  6. "url"])
  7.         //if not just redirect the user to our main page
  8. "http://stuff.nekhbet.ro");
  9.  
  10. //decode our tracked url
  11. "url"]));
  12.  
  13. //insert the track into the DB
  14. $track->InsertTrack("$url");
  15. //redirect the user to the tracked url
  16. "$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 :

  1. span style=”color: #ff0000;”>"/path/to/track.php?url=""$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.