DIY: Shorten Your URLs and Count The Stats
Normally with long URLs that you have from blog and maybe your affiliate links, you go to TinyURL.com, SimURL or XRL.us to get it shorten. The problem with these services is that you don’t know how many times your link has been accessed.
Here’s a DIY thing that you can do. But I have to warn you, this is a simple dirty trick. There’s no fancy setup and any admin interface for this. You have to manage everything from PHPMyAdmin.
First you create yourself a new database. I named mine as ‘utils’ as in utilities. I figured that I would probably create a number of other DIY things after this. Why not just create a database for it. Then create a new database user. Then assign the user to that database. You can do all that in CPanel.
When you are done with that, you launch PHPMyAdmin and select the database that you created. Click on the SQL tab and copy and paste the following SQL command into the textarea.
CREATE TABLE traffic (
stamp datetime NOT NULL default ‘0000-00-00 00:00:00′,
id varchar(10) NOT NULL default ‘’,
ip varchar(20) NOT NULL default ‘’,
referrer varchar(255) NOT NULL default ‘’
);
Let me explain. The code above will create a new table. It’s called ‘traffic’. And it has four columns. The first column is a timestamp of when the redirection occured. The second is the ID of your redirection. The third will store the IP address of your visitor (unless they are behind a proxy server). And the last column will store the referring web page that your visitor comes from.
OK … we are done with that. Not it’s about creating your redirection file. Here’s one example.
< ?php
$db_conn = mysql_connect('localhost', 'user', 'password');
mysql_select_db('utils');
$sql = "insert into traffic (stamp, id, ip, referrer) values ";
$sql .= "(NOW(), 'your-id', '$_SERVER[REMOTE_ADDR]', '$_SERVER[HTTP_REFERER]')";
$result = mysql_query($sql) or die(mysql_error());
header('Location: http://your-long-url.com');
?>
This is a PHP file. It has to have a ‘.php’ ending or it won’t work. And you can name this file anything you like. One file for one redirection. Of course you need to change ‘user’ and ‘password’ with the user and password that you created earlier. ‘utils’ in line two should changed with your database name.
In line four, you should see ‘your-id’. Change that to any ID you want. If I am creating a redirection for Affiliate Project X, I may put in ‘apx’. If I am creating a redirection for Affiliate Project X that I will use in an ezine add, I may put in ‘apx-ezine’. But remember, with the SQL code above, the ID column can only store upto 10 characters.
Lastly look at line number 6, you should substitute the URL with the long URL that you want to shorten. Alright that is all …
And how to do you check for the traffic. Easy - use PHPMyAdmin again. Just choose your database, and then select the traffic table. There you can browse the table, do searches on a certain redirect ID you created or anything …
Oh yeah … I know that some hardcore, experience PHP programmer may read this and say, “I can do better than this?” Yeah, I know. But I said earlier, this is supposed to be an easy, simple, dirty trick. So no fancy stuffs … just quick and easy.
