Creating a short URL service using PHP and MySQL

Hey guys, time to get deep, i thought i would create a short tutorial on creating a URL shortening service. We have all seen them, there are tonnes out there, the most famous one would be tinyurl.com which isnt actually that short as there are plenty out there that are shorter- i own folo.me which is my personal contribution and this a tutorial on what i went throught to create that site. TinyUrl.com is the default one used on Twitter.

In this tutorial am going to discuss several topics-

  • php
  • mysql
  • abstraction of code from design
  • string manipulation
  • mod_rewrite

So lets get started!..

So where to start? Firstly we must setup a database, on your web host or local machine. i would advice setting up a webserver with php and mysql, theres many packages that do this automatically for you including xampp which can be found by clicking here


We must create a database, load up phpmyadmin and typpe a name for your database into where it says create  you can change the collation if you need to but since where just dealing with URL’s there should be a need to so just click create and accept the default collation. (Your phpmyadmin may differ from mine since am using an older version)

creating a database with phpmyadmin
creating a database with phpmyadmin

Once you have your shorter_urls databse created, its time to add some tables to the database, i always assign the first field to be an identifier field with the name ID, even if i dont plan on using the table in relationships i may do so later so its good to name the first field as a primary key. This should be of type integer and set to auto increment and also set as the primary key.

The second field will be the “short” part of the short url, for my site it wil be folo.me/xxxxxx where the xxxxxx would be the short part. This could a varchar and we should define the size as 6 as this would leave us with plenty of room- we are going to use letters and numbers- that gives us 36 charachters to use therefore 36 to the power of 6 would give us 2,176,782,336 which is plenty of space to store URLs.

The next field will be the actual URL that is being shortened- URLS can vary in size, and its safe to say 1000 length would be enough room and as we are going to use varchar for the field as well, varchar doesnt store white space in database we should be fine.

The next field will be a timestamp, a simple datetime field type which we will populate when we insert queries. this will allow us to compile stats and issue querys to see what was popular on certain days.

So this is how our create statement will look like.

CREATE TABLE `short_urls` (
`id` int(11) NOT NULL auto_increment,
`short` varchar(6) NOT NULL,
`url` varchar(1000) NOT NULL,
`stamped` datetime NOT NULL,
PRIMARY KEY  (`id`),
KEY `short` (`short`)
) ENGINE=MyISAM  DEFAULT;

I also defined an index on the short field to speed up searching.

Now that we have the database setup we need to create some php! I like to create my HTML templates first in a nice wysiwyg program like dreamweaver. All you need to do is create a index.php file and insert the following form.

<form id="shorten_form" name="shorten_form" method="post" action="shorten.php">
<label>
<div align="center">
<input type="text" name="url" id="url" />
</div>
</label>
<label>
<div align="center">
<input type="submit" name="submit" id="submit" value="Shorten" />
</div>
</label>
</form>

This form now gives us this-

creating a database with phpmyadmin

Now to create the functions, there are a few we will use-

  • We need a function open the database connection and select the database
  • We need a function to generate our short url
  • We need a url to check if the shortened url is unique or not and if it is then insert the url into the database and return it to the user

So now create your second PHP  file and calll it shuffle.php and insert the following code

function dbconnect(){
   $link = mysql_pconnect('Your_DB_HOST', YOUR_DB_USERNAME', 'YOUR_PASSWORD');
   $db_selected = mysql_select_db('YOUR_DB', $link);
}

This function opens a persistent connection to the database server with your username and password, usually the default username for mysql is “root” and the password is left blank. The persistent conenct is closed when the script is executed.

The next function is to generate the url/xxxxx bit- the actual shortened bit.

function generateurl($numAlpha=6)
{
   dbconnect();
   $listAlpha = 'abcdefghijklmnopqrstuvwxyz0123456789';
   return str_shuffle(
      substr(str_shuffle($listAlpha),0,$numAlpha)
  );
}

In this function, we have defined a parameter $numAlpha equal to 6, this is the length of the string we are looking to generate, the next line is the dbconnect function we defined previously, the next like is a list the charachters that are allowed to be used in the short URLS. Then the return statement which returns the generated string but also does the generating of the string. It uses the php function str_shuffle to randomnize the list defined in $listAlpha, but since it is inside the substr function which is used to cut strings to the length defined in $numAlpha. The return statement in this line – randomnizes the list of charachters, cuts 6 out of the string and then returns it.

function geturl($in)
{
   dbconnect();
 
   do{
     $out = generateurl();
     $query = "SELECT short  FROM `short_urls` WHERE `short` = '$out'";
	 $result = mysql_query($query);
   } while (mysql_num_rows >1);
 
   $insert_query = "INSERT INTO `short_urls` (`id`, `short`, `url`, `stamped`) VALUES (NULL, '$out', '$in', NOW());";
   $result = mysql_query($insert_query);
 
   return "http://YOUR.COM/".$out;
}

This is another nifty little function first in the parameter is the $in parameter, this is the unshortened URL that your are going to send to the function and it will return the shortened url after it has been inserted into the db. The next line of course is our dbconnect function from above. The next this is the do… while loop which will create a shortened url with the generateurl(); function then create a query to check that the generated short url hasnt been taken already, thanks to the do… while loop, if thay short url has been used before it will just generate another one until it finds a short url that hasnt been inserted into the database. Then the next line in the function will be that query that actually insert the generated short_url into the database, then the following line will be the mysql query which will insert it into the database. Then the return statement returns the short plus whatever url you wish, dont forget the trailing slash after your domain!

Thats nearly all the php we need! Three simple functions a bit of html! Well just some more php… To handle the redirect that you need so that yourdomain.com/xxxx redirects to the original long url.

Putting it all together!

Now that we have all our functions is its just a matter of sticking the function calls into the html.

Now create your third php page! We will call it forward.php.

First thing to do in this file is open the php file with the usual

include 'shuffle.php';

Then this-

$i = $_SERVER['QUERY_STRING'];
 
if (preg_match("/^[0-9a-z]{6}$/", $i)) {
    dbconnect();
 
    $result = mysql_query("SELECT short, url FROM `short_urls` WHERE `short` = '$i'") or die(mysql_error());
 
    if (mysql_num_rows($result) < 1) {
        header("Location: http://yourdomain.me");
        exit;
    }else
	{
            $row = mysql_fetch_row($result);
	    header("Location: ".$row[1]);
	}
 
}
else
{
   header("Location: http://yourdomain.com");
}

First line assign the query string to a variable which is just everything that appears after the slash of a domain example.com/querystring. Then the next line is a regular expression to make sure its a short url and not one of your other files on the server. Then as before we connect to the database with our function, then we pass the short url into a mysql query to check if the short url actually exists, and if it does what it corresponds to. Then we do some error checking, if the short url doesnt exist we direct the user to yourdomain using the header function but if it exist we use the header function to redirect the user to the long url that matches the short in the database.

You need to upload the following as the .htaccess file on your server

XBitHack      Off
RewriteEngine On
RewriteCond   %{REQUEST_URI} \/([0-9a-z]{4})$ [NC]
RewriteRule   ^(.*) http://www.yourdomain.com/forward.php?%1 [L]

This parses the query string from yourdomain.com/xxxxxx to the forward.php which is the bsis of the short url service.

Making the it work!

Next thing is to create the final page and combine it all-

create a file called shorten.php and as before include the shuffle.php -

include 'shuffle.php';

Then we include the functions into your template!

         <p align="center">Your URL:</p>
         <p align="center"><?php echo $_POST['url']; ?></p>
         <p align="center">Has been shortened to:</p>
         <p align="center"><?php echo geturl($_POST['url']); ?></p>
 
The second line denotes the variable that was posted from the form on the index.php file then on the last line we use our geturl() function to do carryout the generation and insertion of the urls- return is a nicely shortened url! and thats us!

In the next installment-

  • Using ajax to generate shortened urls
  • Improving security to prevent cross site scripting and junk urls being inserted

Any questions post them below!

View Comments

  1. Hello… nice post!! It will be very useful for me… I miss the use of ajax to generate the shortened URLs as you stated :( .. but thanks anyways!!

  2. i will update this next week for you then :) will email you when its online

  3. What would you like to know?

  4. Hi !!  thanks for the post. !! Is the ajax post online .. How to access?

  5. Its very nice to know more about Creating a short URL service using PHP and MySQL.Its explore our-self.
     

  6. Nice post buddy, thanks, its very helpful.

  7. Hi,

    Thanks for posting this, it was pretty useful.

    I was thinking, since you already have an index on `short`, you can might as well make it unique, and in the do_while loop inside your geturl() function, just try to do an insert and check for mysql error 1062 (duplicate key).

    This way, you can skip doing a second query if the generated string doesn’t match anything.

    I have some concerns about:
    - how much processor and memory does str_shuffle use up?
    - once you have a lot of shortened urls in your database, the chance of getting a hit increase and you have to go through the do_while loop a lot of times (in this case it actually might be better to lookup the short url with select since the index can help you – i’m not sure how MySQL handles inserts and unique keys).

    Has you algorithm evolved in any way since you posted this?

    Thanks!

  8. Be careful, there is an error in htacess, replace “4″ by “6″

    put
    RewriteCond %{REQUEST_URI} /([0-9a-z]{6})$ [NC]

    and no

    RewriteCond %{REQUEST_URI} /([0-9a-z]{4})$ [NC]

    Thank you for the script !

  9. Be careful, there is an error in htacess, replace “4″ by “6″

    put
    RewriteCond %{REQUEST_URI} /([0-9a-z]{6})$ [NC]

    and no

    RewriteCond %{REQUEST_URI} /([0-9a-z]{4})$ [NC]

    Thank you for the script !

  10. For me, the Ajax part was best. I love this foaming cleanser!

Leave a comment

blog comments powered by Disqus