Track outbound link stats easily
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; ?>. 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.
1 Comment »
RSS feed for comments on this post. TrackBack URL
[...] Track outbound link stats easily (tags: php programming resources design code blogs blogging blog articles development free geek generator hacks ideas wordpress webdesign webdev wevdev web tutorials tutorial tools tips mysql work business web2.0) [...]