How to track outbound links with PHP

Shashwat Srivastava

Shashwat Srivastava

— 2 min read
How to track outbound links with PHP

It is important for a website administrator to track the outbound links from his/her webpages. This tells you which links are most popular amongst the users. Here, I will show you how to accomplish this task. To track and count the outbound URL clicks, we will use the following workflow:

  • Direct users to common PHP file, say count.php, with each link having a unique value for query parameter - link_id.
  • Next, store the click details in MySQL database.
  • Finally, redirect the user to the actual URL.

This technique enables us to hide the actual URLs as all the links point to count.php with unique link_id. We will create a new database table to store the URL statistics with the following SQL:

CREATE TABLE `link_count` ( `link_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `URL` TEXT NOT NULL , `count` INT NOT NULL DEFAULT  '0' );

Here, we will store all our outbound URLs along with a unique link_id number. We use this ID number to reference the URL in links and in the tracking. Also, while inserting the details of URLs in this table set the value of count field for them as 0.

Now, create a new PHP file and name it as count.php. Add the following code to it:

<?php /** Connect to DB */ mysql_connect("localhost", "username", "password") or die(mysql_error()); mysql_select_db("DB_Name") or die(mysql_error()); $link_id = $_GET['link_id']; /** Increase the counter of the URL to which the user is going*/ mysql_query("UPDATE link_count SET count = count + 1 WHERE ID = $link_id") or die(mysql_error()); /** Retrieves URL */ $result = mysql_query("SELECT * FROM link_count WHERE ID = link_id") or die(mysql_error()); $row = mysql_fetch_array($result); //redirects them to the link they clicked header( "Location:" .$row['URL'] ); ?>

Now, suppose we have an entry in our table link_count as shown below:

INSERT INTO `link_count` ( `link_id` , `URL` , `count` ) VALUES ( '1',  'http://www.botskool.com',  '0' );

URL details in SQL table

The link for this URL on your actual page would look like this:

<a href="http://www.yoursite.com/count.php?link_id=1">bOtskOOl</a>

If the user clicks on this link, first the count value for this URL will be updated in the database. After this the actual URL will be extracted from the database. Finally, the user will be re-directed to this URL.

Read more