Short URL Outbound Link Tracking in PHP and MySQL

Question

I have a site that shortens links based on Noah Hendrix's tutorial on the subject. I decided that it would be great if I could track when users click the short URLs, similar to the way that HootSuite users can track their links with Ow.ly. I currently have a database which has the short URL stored along with the true URL and it's click count. Ideally the click count column would update when that short URL is accessed by an outside user.

In short, I am looking for a PHP/MySQL solution to keep track of the number of times various short URLs are clicked. Any additional information that could be gathered from the clicks would be greatly appreciated as well.

Answer 1

I am assuming you followed the php version of his tutorial. If so look at the listing for serve.php under "Serving the Short URL". In the section round line 11 where it sets the 301 status you can log the redirect there with an update to the database. Something like

 $query = mysql_query("update `".$database."`.`url_redirects` set count=count+1 where `short`='".mysql_escape_string($short), $db);  
 $row = mysql_execute_update($query);

should do it.

Answer 2

Here's a round-about alternative--How about a non-brain damage approach? Try putting Google Analytics on the site. You'll not only get a click report, but can also track paths through the site, ins vs outs, network properties, user locations, etc. It's a simple javascript include, and takes all of 5 minutes to setup start to finish.

I've been a PHP developer for a long time, and my personal theory is that there's a lot of challenges that need to be solved out there, no reason to waste time on solutions others are willing to give you...