http://www.developer.com/

Back to article

Build a URL Shortener with jQuery, PHP and MySQL


July 28, 2010

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.

Step 4. Integrate the Ajax

With the PHP script tested, let's add the Ajax enhancement. Of course, you'll need to integrate jQuery, which is easily and efficiently accomplished by referencing it via Google's content distribution network (Google Libraries API).

Beyond including the jQuery library, the following snippet will attach an event handler to the form's submission action. When the form is submitted, the value passed to the form's url form field is retrieved and passed to the shorten.php script using jQuery's $.POST function. Finally, the DIV identified by the response ID will be populated with either an error message or the abbreviated URL depending upon the outcome of the shorten.php script.

<script src="http://www.google.com/jsapi?key=YOUR_GOOGLE_AJAX_API_KEY" type="text/javascript"></script> <script src="http://www.google.com/jsapi"></script> <script type="text/javascript"> google.load("jquery", "1.4.2"); </script> <script type="text/javascript"> $(document).ready(function(){ // Associate an event handler with the form submission action $('#form_url').submit(function(e) { // Retrieve the form's URL field value var url = $('#url').val(); // POST the URL to the shorten.php script $.post( "shorten.php", {url: url}, function(data){ if (data == "FALSE") { $("#response").html("There was a problem shortening your URL"); } else { $("#response").html("Your shortened URL: <a href='" + data + "'>" + data + "</a>"); } }, "text" ); // Prevent the form from actually submitting e.preventDefault(); }); }); </script> 

Step 5. Create the Redirection Script

The redirection script (redirect.php) is responsible for converting the abbreviated URL into its elongated counterpart, in addition to adjusting the row's clicked column upwards. Like the shortener script, this is pretty straightforward: it first verifies that the provided abbreviation exists and if so, it adjusts the click count before redirecting the user. Here's the script:

<?php define("SERVICE", "http://u.wjgilmore.com/"); $db = new mysqli("localhost", "webuser", "test", "shortener"); // If an abbreviation has been provided, process it if (isset($_GET['abbreviation'])) { $abbreviation = $_GET['abbreviation']; $stmt = $db->prepare("SELECT url, clicked FROM urls WHERE abbreviation = ?"); $stmt->bind_param("s", $abbreviation); $stmt->execute(); $stmt->store_result(); // Was the abbreviation found in the database? if ($stmt->num_rows == 1) { $stmt->bind_result($url, $clicked); $stmt->fetch(); $clicked++; // Update the click counter $stmtUpdate = $db->prepare("UPDATE urls SET clicked = ? WHERE abbreviation = ?"); $stmtUpdate->bind_param("ds", $clicked, $abbreviation); $stmtUpdate->execute(); // Redirect user to appropriate URL header('Location: '. $url); } else { echo "This URL does not exist!"; } } ?> 

Because we want the URLs to be as compact as possible, it wouldn't do to include the redirection file name (redirect.php) in the abbreviated URL. To remove the filename, we can use a simple .htaccess file to redirect all strings that don't match a filename or directory found in the website's home directory to redirect.php:

RewriteEngine On RewriteCond %{REQUEST_FILENAME} !-d RewriteCond %{REQUEST_FILENAME} !-f RewriteRule ^(.*)$ redirect.php?abbreviation=$1 [QSA,L] 

Where to From Here?

The URL shortener service is operational! Of course, there's plenty of room for improvement, as I took a number of shortcuts in order to fit the code and discussion into a format suitable for publication here on Developer.com. Grouping configuration information into a single file would be an improvement, and it would certainly be beneficial to refactor much of the functionality into an object-oriented class. Additionally, some additional checks should be implemented to ensure that a duplicate random abbreviation isn't generated, which although highly unlikely is still possible.

Just for kicks I'll continue working on the code as time allows. You can monitor the project and download the code at github.com/wjgilmore/yoyo.

Conclusion

Building a link-shortening service is a fun and educational way to get acquainted with the intricacies of creating a functional, Ajax-enhanced website. Try experimenting with the code and adding your own features! And as always, if you do anything cool with it, let us know in the comments!

About the Author

Jason Gilmore is founder of WJGilmore.com. He also is the author of several popular books, including "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Third Edition".

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date