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.