« Send Direct Message to Twitter Upon Gmail || .htaccess Generator Tool »

March 6th, 2008

Track outbound link stats easily

1


Ever wonder which links are the most popular on your site? I'm always curious which links receive the most attention and who I'm giving my traffic to so I developed this "quick-n-dirty" outbound link tracking script that runs from a mysql database (if you're still running flat file scripts, time for an update...no one does that anymore and haven't for a long time...shame on you).

tl.php (for tracklink...can be named anything, obviously)

 
<?
//KevinSmithDesigns.com
//connect
mysql_connect("localhost", "un", "pw") or die(mysql_error());
mysql_select_db("") or die(mysql_error());$url = $_GET['ksdurl'];
 
$comment = $_GET['ksdcomment'];
$dateadded = date('Y/m/d'); // 4 dig year, 2 dig month, and 2 dig day -- makes for the easiest and cleanest sorting later on!
 
//Check if url already exists WITH the specific comment...
$exist = mysql_query("SELECT * FROM links WHERE url='$url' AND comment='$comment'");
$doesit = mysql_fetch_object($exist);
if($doesit->url == $url) {
mysql_query("UPDATE links SET clicks=clicks+1 WHERE url='$url' AND comment='$comment'");
header("Location: $url");
} else {
 
//insert vars
$insertlinksquery = "INSERT INTO links (url, created, clicks, comment) VALUES ('$url', '$dateadded', '1', '$comment')";
$runinsertlinksquery = mysql_query($insertlinksquery) or die(mysql_error());
 
header("Location: $url");
}
 
?>
 

Now how to activate this script so that clicks are recorded: If you link to google.com, your new url would be: http://domain.com/tl.php?ksdurl=http://google.com. If you link to google multiple times and want to track each link, add a comment: http://domain.com/tl.php?ksdurl=http://google.com&ksdcomment=from about page Like always, this can be done better and more efficient. However, this is something I've quickly thrown together to track a few links simply to satisfy my curiosity. I'd suggest adding in $_SERVER['HTTP_REFERER'] to automatically track the source AND it would also be a good idea to use htaccess to automatically log links in tl.php...I'm just not going to take the time to do that. ----- You could customize the display of the stats to your own (mine is fairly detailed and complicated compared to the above script). Here's an example:

 
<?
$sortby1 = $_GET['sort'];
if($sortby1 == ''){ $sortby = 'id'; } else { $sortby = $sortby1; }
//don't forget to connect to the db
?>
<html>
<head>
<title>Link Tracker</title>
</head>
<body>
<font size="6" face="Verdana">Link Tracker</font><br>
<font face="Verdana" size="2">Currently sorting by <? echo $sortby; ?>.&nbsp; Reorder by: Sort by: <a href="stats.php?sort=id">ID</A> | <a href="stats.php?sort=url">URL</A> |<a href="stats.php?sort=created">Created</A> |<a href="stats.php?sort=clicks">Clicks</A> |<a href="stats.php?sort=comment">Comment</A></font>
<table border="1" width="850" cellspacing="0" bordercolor="#000000" bordercolorlight="#000000" bordercolordark="#000000">
<tr>
<td width="51" align="center" valign="top" bgcolor="#C0C0C0">
<p align="center"><font face="Verdana" size="1">ID</font></td>
<td width="72" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">CLICKS</font></td>
<td width="99" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">ADDED</font></td>
<td width="385" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">URL</font></td>
<td width="209" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">COMMENT</font></td>
</tr>
 
<?
$getlinks = mysql_query("SELECT * FROM links ORDER BY $sortby");
while($getlinks1 = mysql_fetch_array($getlinks)){
$id = $getlinks1['id'];
$url = $getlinks1['url'];
$added = $getlinks1['created'];
$comment = $getlinks1['comment'];
$hits = $getlinks1['clicks'];
echo "
<tr>
<td width=51 align=center><font face=Verdana size=1>$id</font></td>
<td width=72 align=center><font face=Verdana size=1>$hits</font></td>
<td width=99 align=center><font face=Verdana size=1>$added</font></td>
<td width=385 align=left><font face=Verdana size=1><a href=\"$url\">$url</a></font></td>
<td width=209 align=left><font face=Verdana size=1>$comment</font></td>
</tr>
 
";
}
echo "</table>
 
";
?>
</body>
</html>
 

And, finally, sample database structure:

CREATE TABLE `links` ( `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT, `url` TEXT NOT NULL, `created` VARCHAR(20) NOT NULL DEFAULT '', `clicks` VARCHAR(10) NOT NULL DEFAULT '', `comment` VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 

Additions, comments, suggestions all welcome.


This entry was posted on Thursday, March 6th, 2008 at 10:49 pm and is filed under Blog, MySQL, PHP, WebDev. 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.

Get a Trackback link

1 Trackbacks/Pingbacks

  1. Pingback: links for 2008-03-11 at Kevin Smith on March 11, 2008

Leave a comment