PHP

Hacking Lessn To Track Statistics

Posted on by Gregg Hilferding

When Shaun Inman shared his URL shortening PHP script Lessn, I was pretty excited.

Right now I use kl.am, a free service from Raven SEO Tools, for all my URL shortening needs. It’s awesome and 99.5% perfect. But I’ve really wanted to host my own for three reasons:

  1. I want bulk uploads with custom short URLs so I can have predictable, product SKU short URLs.
  2. I want to be able to repoint a short URL if it’s old URL changes or 404s.
  3. I don’t want to compete with other users for custom URLs.

Unfortunately, rolling my own will mean I miss out on all the amazing coolness of kl.am. Mine wouldn’t be as slick or easy to use. However, Lessn is slick and easy to use. But it doesn’t do custom URLs and it doesn’t track how often people visit a short URL. So I added those features.

Tracking clicks

In the file /-/db.php, add this statement to create a second table:

mysql_query('CREATE TABLE IF NOT EXISTS `'.DB_PREFIX.'stats` ( '.
'`id` int(11) unsigned NOT NULL auto_increment, '.
'`url_id` int(11) unsigned NOT NULL, '.
'`ts` datetime NOT NULL, '.
'`ua` text character set utf8 collate utf8_unicode_ci NOT NULL, '.
'`ref` text character set utf8 collate utf8_unicode_ci NOT NULL, '.
'`ip` int(4) unsigned NOT NULL, '.
'PRIMARY KEY (`id`), '.
'KEY `url_id` (`url_id`) '.
') ENGINE=MyISAM DEFAULT CHARSET=utf8;');

Then, in the /index.php file, add this line right after the header(‘Location:’); line:

mysql_query("INSERT INTO `".DB_PREFIX."stats` SET `url_id`='".$row['id']."', `ts`='".date("Y-m-d H:i:s")."', `ua`='".mysql_real_escape_string($_SERVER['HTTP_USER_AGENT'])."', `ref`='".mysql_real_escape_string($_SERVER['HTTP_REFERER'])."', `ip`='".sprintf("%u", ip2long($_SERVER['REMOTE_ADDR']))."' ");

To test, go visit one of your short URLs, and then go check the stats table. You should see a new entry in the table.

Now, how you choose to display the data from this table is up to you. :)

Gregg Hilferding