HomeWriters RSS

Build It: URL Shortener


Every time you look on Twitter at a URL someone has posted it's been shortened to a url - awesome huh? There's many others out there like What if you want to write your own? Well, you could use your domain with if you wanted, but how about having your own system running on your own server. Well here I guide you through how to do it from the SQL databases, all the way to writing an API for URL shortening.


We're going to be using MySQLi, and a lot of object oriented PHP, for some good measure we'll be throwing in some regular expressions too. We'll start with the database structure and then build a simple shortener class to use. We'll go via logging hits to a URL, and come full circle to writing a neat API that you can use anywhere to generate a URL of your choosing.

Complete source code and video walk through are available at the bottom of the article.

The database


We need a table for the URLs to be stored in, and one for the hits as well. All we really need for the URL is the place to direct to, an ID, IP of who added it and when it was created.

The following SQL will create a table called urls where we'll store all of the shortened URLs:

CREATE TABLE  `urls` (
    `url` VARCHAR( 255 ) NOT NULL ,
    `ip` VARCHAR( 39 ) NOT NULL ,

Nothing too complicated here - we have to ensure that we have the primary key setup as we will be searching based on that - and the last thing we want is to compromise the speed of the queries by not indexing.

Hit counting

For hit counting we'll just store the IP, the time and the URL that the user is accessing:

CREATE TABLE  `hits` (
    `ip` VARCHAR( 39 ) NOT NULL ,

Very similar to our other table, except that we have the URL as an integer here as this will ultimately form as a foreign key to the urls table that we earlier made.

You might at some point want to log what system or method your links were created through - this could be the API or a web interface. Ultimately you could add an extra column to the database called method which would be an integer, with 1 being web and 2 being API - we won't be doing that currently.

The URL Class

Now that we have the database set up as we want we're going to go ahead and create one file called Urls.class.php - this will handle everything that we need, instantiate a database connection, create a URL, check a URL exists etc.


We only want to instantiate the database connection once so we'll assign an instance of the MySQLi object to a private static variable called _connection.

    self::$_connection = new mysqli('localhost', 'root', 'root', 'personal');

Also, you need to ensure that you're storing the user's IP at some point so that you can put it into the database, for this we will be using the value that is stored in the global array $_SERVER, using index REMOTE_ADDR.

Adding a URL

Adding a URL is as simple as running an INSERT query on the url table that we created earlier. We'll be passing through a parameter called $link to the variable which will be the URL that we should store.

We'll be using prepared statements here so that we don't need to worry about escaping characters in the queries. If the query executes successfully you should be sure to return the ID of the inserted link by using the variable insert_id which is in the MySQLi statement.

$query = self::$_connection->prepare("INSERT INTO `urls` (`url`, `ip`) VALUES (?, ?)");
$query->bind_param('ss', $link, $this->userIP);
return $query->insert_id;

Be sure to include error checking as well with all of your database interactions.

Getting a URL

A simple SELECT statement which is just going to be passed an ID to get from the database. This can pretty much follow the same process as the previous method, except returning the result URL.

Nice and simple ...

$query = self::$_connection->prepare("SELECT `url` FROM `urls` WHERE `id` = ?");
$query->bind_param('i', $id);
return $url;

This just runs the query, binds the result to the variable $url and then returns that URL for us to use - perhaps to redirect the user to.

Storing a hit

Storing a hit is nothing spectacular, we're just going to insert a new row into the hits table every time that someone access the link.

$query = self::$_connection->prepare("INSERT INTO `hits` (`url`, `ip`) VALUES (?, ?)");
$query->bind_param('is', $linkId, $this->userIP);
return TRUE;

Nothing to return other than a success so that we know that the query has been executed, of course you should be doing some error checking to ensure that the query has actually executed - don't just assume that it has.

Class complete

Now that we've got the basics of the class written, we can look to write some of the other pages. There are 2 other pages to write:

  1. The page that the user gets taken to as the short URL page
  2. The page that gets the URL to shorten, shortens it and returns the short URL.

Redirect user to short url

To start with we'll just have the user go to a page called redirect.php which will take a GET parameter of id which is an integer - so for example for URL 7 we'd have them go to redirect.php?id=7.

On this page we will look up the URL, and if there is a result we'll add a hit and then redirect them off to the URL. If there is no result we'll just say that there's no result to redirect the user to. To check if the URL exists we'll just look for a NULL response.


$l = new Urls();
$link = $l->getShort($_GET['id']);

    die('Unknown short URL.');


header('Location: ' . $link);

To redirect the user off we can use a simple header function setting the Location parameter as above. Remember always to add in error checking - making sure that the actual URL exists and that the user has set an ID to redirect off to.

What if...

This looks ugly - it defeats the object to have people going to or whatever to redirect. Lets look at how to glamourise this in two ways:

  1. An alphanumeric id for the url eg: ahc1
  2. URL such as

We'll need to write a method that will convert from an ID to a nice string, and one that will convert back. We can do that using the following methods:

function numToAlpha($num){
    $return = "";
    $alpha = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-_";
    $n = floor($num/strlen($alpha));
    if($n > 0)
        $return .= numToAlpha($n);
    $return .= $alpha[$num % strlen($alpha)];
    return $return;

function alphaToNum($s){
    $alpha = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-_";
    $return = 0;
    $i = strlen($s);
    $s = strrev($s);
        $return += strpos($alpha, $s[$i]) * pow(strlen($alpha), $i);
    return $return;

You can build these in to your URL class as static methods, also you can make them use the same $alpha string too.

Once you've done that, we can now look to change our previous redirect code a little, where we were previously passing in $_GET['id'] to the getShort() and addHit() methods we can now pass in Urls::alphaToNum($_GET['id']) where id is the shortened URL string such as ahc1. You can test that's all working by using the same redirect.php?id=ahc1 path.

Now let's make that neater, we'll need to write a .htaccess rule to change any URL ending one of our predefined characters to the reirect path. To do this we use a thing called mod_rewrite. Firstly we need to construct a regular expression that will match any of the characters in our $alpha string, we can do that using the following:


This will match 1 or more occurances of the items within the square brackets, we can then choose where to put that in the rewrite by using the string $1:

# Turn on the rewrite engine
RewriteEngine On

# Don't rewrite any directory or file that exists: 
RewriteCond %{REQUEST_URI} !-f
RewriteCond %{REQUEST_URI} !-d

# Rewrite!
RewriteRule ^([a-zA-Z0-9_\-\+]+)$ redirect.php?id=$1

With that, you should be able to go to and it will automatically do exactly the same as going to the redirect.php path. You can now place this .htaccess file (that's the name of it by the way) in the same directory as the redirect.php file. This would normally be the domain's root directory.

Page to shorten URL

Finally we need a page to shorten the URL - this will simply take in a URL, insert it into the URL and then kick back the url that the user should use. Remember, this will no longer be an id at the end of the URL - this will be a string of characters from our earlier $alpha string.

The long and short of it is as simple as doing this:


    die('Please provide a URL to shorten.');

$l = new Urls();
$linkId = $l->addShort($_GET['url']);

echo '' . Urls::numToAlpha($linkId);

You could add in the ability to check if a URL exists by adding a method to the Urls class called checkShort() for example.

The End

That is the basic idea behind creating a URL shortener, however there are many other features that you could add in such as:

Overall, you should now be able to create and run your own URL shortening service - go have a play!

Support and Download

All of our Build-It articles are more full featured than our basic tutorials in that we offer a video tutorial as well as full source code to download. However, to maintain Codular, we offer these at a very low cost of $2.99. Don't look at it as paying for the files, see it as a donation, but getting something awesome back.

If you're interested in downloading simply click here to do so, PayPal is required for now. Look out for us on Twitter where we might give away discount codes for up to 50% off.

Tags: PHP, MySQL