BL.INK # MatchURL : No. 1 Premium URL Shortener

BL.INK # MatchURL : No. 1 Premium URL Shortener


Lengthy URLs are one of the most common complaints I get while reading comments on blogs. Whether people say it's hard to cut and paste them or that they're too long, it's always something people wish there was a better alternative to.

I've written before about how other sites like Facebook and Reddit shorten their links with services like bitly , which makes sense because they want to make sharing easy. But what if you wanted an easy way for your readers to share your content without having the link shortened by the service? It should be relatively simple; just build a tool that generates short links from longer ones!

Today I'm going to show you how I built my own URL shortener using PHP/SQL, called

Step 1: What you need

To build a URL shortener, you'll obviously need a web server. If your hosting service didn't include one by default then I'd recommend going with a cheap Linux VPS from DigitalOcean. They're regularly on sale and offer pretty good specs for a low price ($5/mo). You can also use Heroku's free tier for this particular project if you have no budget to work with at all. Really any web server will do, as long as it can run PHP or Perl scripts so we can easily access the database server without too much hassle.

URL Shortener Requirements: MySQL Database Server (most of these instructions assume MySQL) Apache Server with mod_rewrite enabled Ability to write some PHP or Perl to interface with the database

It's pretty simple, but it'll be a lot easier if you have your hosting set up already so you can just upload all of your files over. If you're not familiar with how to do this, I'd recommend reading through an introduction on WordPress since most web hosts are compatible with that kind of architecture.

Step 2: The Data Table

So now that we have our server ready, let's start by building out our table schema. We're going to go with MySQL for this example because it's widely known and used, but feel free to create an equivalent in whatever backend language you want! The main things we need are relationships between URLs and IDs which will be unique across each individual entry.

The table will look something like this: CREATE TABLE short_urls ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , url VARCHAR ( 255 ) NOT NULL UNIQUE , UNIQUE KEY ( url ) );

The main unique key in the table is essentially just to know that each individual URL must have a different identifier. We'll use this to our advantage when we write the actual URLs so they don't conflict with one another since they're all stored in the same place. The prefix can be anything you want, but for this project I went with bl . I also used the variable type VARCHAR for url because it allows up to 255 characters which makes sense because pretty much every URL is going to be longer than that.

The UNIQUE KEY tells MySQL that at least one of these must exist for each entry in the table, and the PRIMARY KEY makes sure this field is included. My naming scheme might seem a little odd since I used ID instead of just 1 , but it's because we'll be referencing them with an AUTOINCREMENT field which essentially means "increase by 1 automatically". This way when we add another record, if there was already an id with a value of 3 in the database then ours will have a value of 4 . While you don't need this specifically when building a URL shortener, it'll be helpful later when making our actual links.

Step 3: The Database

Now that we have our table schema, let's add some records to see how it'll look! In this case I named the database "urls" and each record is stored in a file at [database]/short_urls.sql . Since MySQL is so widely used, it should be easy for you to just copy paste the short_urls.sql over into a new database if necessary. If you're not familiar with how to do this then feel free to just click around your hosting service's website until you've figured out how to create a new database or import SQL files.


INSERT INTO `urls` ( `id` , `url` ) VALUES ( 1 , '' ), ( 2 , '' );

Now that you have a few records in the table, let's run some queries to be sure everything is working before we start trying to build our tool! To see all of your short_urls just do SELECT * FROM urls . If you put more than one record into the table above then it'll show up as SELECT * FROM urls WHERE id IN (1,2) or something similar depending on how your host handles subqueries. If you only put one record in then it should look something like SELECT * FROM urls .


SELECT * FROM urls WHERE url = '' LIMIT 1 ; SELECT * FROM urls WHERE id IN ( 2 , 3 , 4 );

If everything looks good up until now, you're probably ready to start working on building out the tool! Feel free to refer back to this database structure if you need it later on after moving forward.

Step 4: Building Our Shortener Tool

Now that we have our table schema and some records in the database, let's get started with actually building out our URL shortener! This section will be more of a tutorial where I'll show you what all goes into making the main script which will handle both adding new records and adding links to existing ones. This way, if you don't want to go through with actually building this out (to only use it yourself or save the code for later), you'll at least have all of the knowledge required to do it on your own!

The following is the entire main script; save it as in your project directory. You can download it here  if that's easier for you.

#!/usr/bin/python3 import MySQLdb , re def add_short_url ( table , id ): cur = MySQLdb . connect ( host = 'localhost' , user = 'root' , passwd = 'password' , db = 'urls' ) cur . execute ( """);

We are social