How to make a PHP & MySQL URL shortener script for the web in 20 minutes

For our next tutorial, we’ll show you how to create a simple URL shortener script in PHP and MySQL in 20 Minutes. You’ll need PHP and MySQL setup, ready to begin programming.

Creating the MySQL database

First, let’s create a database table in MySQL to store our URL’s. You can use a software tool like phpMyAdmin, MySQL Workbench or Navicat 16 for MySQL. We’ll go ahead and create a new database and a new table within it called “short_urls”. The table should have at least the following columns:

• id (auto-incrementing, primary key, integer)
• long_url (text)
• hash (text)
• created (timestamp)
• number_of_views (int) (default value 0)

Creating the Shortener Page and Form

<form id="create_shortened_url" method="POST" action="MakeMeShort.php">
  <label for="long_url">Enter a Long URL which we can shorten:</label>
  <input type="text" name="long_url" id="long_url">
  <input type="submit" value="Shorten URL">
</form>

This page allows users of the script to transform a long URL into shortened URL. Here’s the html form code above.

Below is the logic which transforms the URL into a shortened version. We’ll create a script call MakeMeShort.php, making sure the form above links to the same path.

<?php

// Connect to the MySQL database using MySQLi Connect
$conn = mysqli_connect('localhost', 'username', 'password', 'database_name');

// Lets sanitize the URL and store it into a local PHP variable.
$url = trim($_POST['long_url']);

//Next we’ll check the url is a valid url, using a built in filter in PHP itself.
if (filter_var($url, FILTER_VALIDATE_URL)) {
echo("$url is a valid URL");
} else {
echo("$url is not a valid URL");
exit;
} 

// Now we’ll transform the long URL into a hash representation of the url, were going to use MD5 and add a salt so it’s unique to this script.
$salt = “Rrv>?W<hyu!asq”;

$hashed_url = md5($salt.$url);

// Now we have our hash representation of the URL lets insert it into our newly created MySQL database.
$query = sprintf("INSERT INTO short_urls (long_url, hash, created_at) VALUES ('%s', '%s', NOW())",
mysql_real_escape_string($url),
mysql_real_escape_string($hashed_url)); 

mysqli_query($conn, $query);

//Now we’ll provide the user with the shortened link – note: you can remove the get parameters for this using mod_rewrite instructions.
echo "Your new URL is: http://yourdomain.com/?r=$hashed_url";

?>

Creating the PHP script which handles URL redirection using headers and visitor count.

Finally, you’ll need to create a script that will redirect users from the short URL to the long URL. Here’s an example PHP code:

<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'database_name');

// Look up the long URL in the database
$query = sprintf("SELECT long_url FROM short_urls WHERE short_url=%s",
mysql_real_escape_string($_GET[‘r’])); 
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

//update the number of views
$query = sprintf("update short_urls SET number_of_views +1 WHERE short_url=%s",
mysql_real_escape_string($_GET[‘r’])); 
$result = mysqli_query($conn, $query);

if(isset($row['long_url']) { 
// Redirect the user to the long URL
header("Location: $row['long_url']");
} else {
header("HTTP/1.0 404 Not Found");
}

die();
?>

Finishing up, and limitations

That’s it! You should now have the basic ideas for a URL shortener script in PHP and MySQL. Test it out by entering a long URL into the form and verifying that the shortened URL works. Again, this is just a quick example of how you can implement url shortening into your own code, obviously, you’ll need to add more functionality and test thoroughly to make sure it’s ready for actual use.