Simple Link Tracking with PHP

Simple Link Tracking with PHP – Part I

This little tutorial will teach you how to create a simple out-going link tracker in PHP.

One of our clients had a rotating-advertisement script we had set him up with, and they decided they wanted to count the number of times each ad was clicked. Easy enough to do, but why just count clicks? There’s so much more information that would be beneficial to capture. Where was the user when the clicked on the ad? When? What browser were they using? In response to their request, I whipped up this little script. This script can be inserted just about anywhere you need to track out-going links.

This tutorial assumes that you already have a database and the appropriate connection already established.

First we set up the database. Create a table as follows:

CREATE TABLE `link_tracker` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`href` text NOT NULL,
`user_agent` text NOT NULL,
`referer` text NOT NULL,
`timestamp` datetime NOT NULL DEFAULT ’0000-00-00 00:00:00′,
PRIMARY KEY (`id`)
)

Easy enough right? This will allow us to track which link is clicked, browser/OS, what page the link was clicked on, and when the link was clicked. Just the basics, but enough to suit the purposes of our client.

Next, we take care of the actual link tracking. Create a file called “jump.php” and fill it with the following:

<?php
$jump_to_link = $_GET[‘href’];
$user_agent = $_SERVER[‘HTTP_USER_AGENT’];
$referer = $_SERVER[‘HTTP_REFERER’];
$str_sql = "INSERT into link_tracker (href, user_agent, ".
"referer, timestamp) " .
"VALUES ( ‘" . $jump_to_link . "’, ‘" .
$user_agent . "’, ‘" .
$referer . "’, NOW() )";
mysql_query( $str_sql ) or die( mysql_error() );
header(‘Location: ‘ . $jump_to_link);
?>

Thats it. Short and sweet. We’re capturing all the information we set out to. Next, you have to set up the links you want to track. This is the easy part. Change your links as follows:

<a href="./jump.php?href=http://your.real.link.here/index.html">
Click here
</a>

Assuming everything went correctly, you should now be tracking links. The next part is pulling the information. Now, I’m not going to write out a complete application for you, I’ll just give you two simple little functions to get you started. You have to take care of styling! Thats right, no hand-holding here!

The first function does just as its name implies. It shows you each link that has been clicked, and display the number
of times. Nothing more, nothing less.

<?php
function show_hitcounts()
{
$str_sql = ‘SELECT COUNT(id) as clicks, href from ‘;
$str_sql .= ‘link_tracker GROUP BY href’;
$result = mysql_query( $str_sql ) or die (mysql_error() );
while( $f_result = mysql_fetch_object( $result ) )
{
echo $f_result->href;
echo ",";
echo $f_result->clicks;
echo "<br/>";
}
}
?>

The next function also does as the function name implies.

<?php
function show_browsercounts()
{
$str_sql = "SELECT COUNT(id) as clicks, user_agent from ";
$str_sql .= "link_tracker GROUP BY user_agent";
$result = mysql_query( $str_sql ) or die (mysql_error() );
while( $f_result = mysql_fetch_object( $result ) )
{
echo $f_result->user_agent;
echo ",";
echo $f_result->clicks;
echo "<br/>";
}
}
?>

And thats it. Easy right? Simply call either of those functions on whatever page you want, and you will have the beginnings of a simple link-tracking system. Hope this helped someone out! Comment or suggestions?

~ digifuzz

Posted on Tuesday, July 10th, 2007 at 7:10 am and is filed under Php. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.