April 18, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Build a URL Shortener with jQuery, PHP and MySQL

  • July 28, 2010
  • By Jason Gilmore
  • Send Email »
  • More Articles »

URL shorteners such as TinyURL and bit.ly are all the rage these days, with their popularity having skyrocketed alongside Twitter and mobile device usage. The idea is a fairly simple one: accept as input an unacceptably long URL and provide in return one that fits into the tiny confines of a mobile device and Twitter's 140-character limit. Afterwards, anytime the abbreviated URL is clicked, the user is first sent to the shortening service, at which point the abbreviated URL is compared with a database, the original URL retrieved, and the user redirected to the desired location.

Beyond being able to provide the immediate utility of abbreviating a lengthy URL, URL shortening services are able to provide users with a number of other useful features, such as clickthrough statistics, referrer details, and even information about link diffusion such as who else has mentioned it on Twitter.

Because such a service encapsulates quite a few useful programming lessons, I thought it would be both fun and educational to create our own URL shortening solution. We'll use several popular tools to build the service, including jQuery, PHP and MySQL. You can also test out the service for yourself by navigating to u.wjgilmore.com.

Step 1. Create the MySQL Table

Let's begin by creating the MySQL table used to store the URLs. At a minimum, this table should include columns for storing the original URL and the abbreviated alias. However, it would also be useful to store the creation timestamp and clickthrough statistics, so we'll include those columns as well:

CREATE TABLE urls ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, url VARCHAR(500) NOT NULL UNIQUE, abbreviation CHAR(5) NOT NULL UNIQUE, created_on TIMESTAMP NOT NULL, clicked INTEGER UNSIGNED NOT NULL DEFAULT 0 ); 

Because a URL-abbreviation service will naturally spend a lot of time scanning the table to determine whether a URL already exists, as well as attempting to look up a URL abbreviation, the associated columns should be indexed to boost performance:

CREATE INDEX urls_url ON urls (url(50)); CREATE INDEX urls_abbreviation ON urls (abbreviation); 

Step 2. Create the HTML Form

Next we'll create a form that will accept the URL the user would like to abbreviate. As forms go, this is a pretty simple one, consisting of just a text field and a submit button. As you'll learn in the next step, jQuery's magical capabilities allow us to enhance the form with an Ajax-driven processor without actually having to complicate the form layout.

<form id="form_url" name="form_url" action="shorten.php" method="post"> <label for="url">Shorten a URL, any URL:</label><br /> <input type="text" id="url" name="url" value="" size="50" /><br /> <input type="submit" name="submit" value="Shorten It!" /> </form> <br /> <div id="response"></div> 

This snippet should be pretty straightforward, except for perhaps the DIV identified by response. We'll return to this later in the tutorial.

Step 3. Create the PHP Script

With the HTML form in place, let's next create the PHP script that will process the request. I prefer to create and test the PHP script before enhancing the site with Ajax, as this approach makes it easier to later isolate problems when debugging other aspects of the application.

The PHP script (shorten.php) is fairly straightforward, taking as input the provided URL and first consulting the MySQL database to determine whether the URL already exists in the urls table. If it does, the associated abbreviation is returned. Otherwise, a new abbreviation is generated and inserted into the database along with the new URL. Should any errors occur along the way, FALSE is returned. Otherwise, the abbreviated URL is returned. Here's the script:

<?php define("SERVICE", "http://u.wjgilmore.com/"); $db = new mysqli("localhost", "webuser", "test", "shortener"); $url = filter_var($_POST['url'], FILTER_VALIDATE_URL); // If filter_var validated the URL, continue if ($url) { $stmt = $db->prepare("SELECT abbreviation FROM urls WHERE url = ?"); $stmt->bind_param("s", $url); $stmt->execute(); $stmt->store_result(); // Does the URL already exist? If so, return the existing abbreviation if ($stmt->num_rows == 1) { $stmt->bind_result($abbreviation); $stmt->fetch(); echo SERVICE.$abbreviation; // URL does not already exist in DB, so generate an abbreviation } else { $chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; $abbreviation = substr(str_shuffle($chars),0,5); $stmt = $db->prepare("INSERT INTO urls VALUES (NULL, ?, ?, NULL, 0)"); $stmt->bind_param('ss', $url, $abbreviation); if ($stmt->execute()) { echo SERVICE.$abbreviation; } else { echo "FALSE"; } } } else { echo "FALSE"; } ?>

At this point we have an operational URL shortener, minus the cool Ajax capabilities. Give it a try by adding a few URLs. In return, you should receive abbreviated versions that look similar to http://u.wjgilmore.com/NRTkK.


Tags: PHP, MySQL, jQuery



Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel