Creating a URL Shortener Application in PHP & MySQL

Hi Guys,
In this post we will learn how to create your URL shortening application in PHP and MySQL.

You may also use this app as an API by calling the URLs from another application.

Step 1: Creating a Table

CREATE TABLE IF NOT EXISTS <code>url_shorten</code> (
 <code>id</code> int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 <code>url</code> tinytext NOT NULL,
 <code>short_code</code> varchar(50) NOT NULL,
 <code>hits</code> int(11) NOT NULL,
 <code>added_date</code> timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Step 2: Create a file index.php and add database credentials in it

$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'shorten_db';
$base_url='http://localhost/myapp/'; 

Step 3: Get the URL in querystring and return a shortened URL using following code:

if(isset($_GET['url']) && $_GET['url']!="")
{ 
$url=urldecode($_GET['url']);
if (filter_var($url, FILTER_VALIDATE_URL)) 
{
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
$slug=GetShortUrl($url);
$conn->close();

echo $base_url.$slug;


} 
else 
{
die("$url is not a valid URL");
}
 
}
else
{	?>
<center>
<h1>Put Your Url Here</h1>
<form>
<p><input style="width:500px" type="url" name="url" required /></p>
<p><input type="submit" /></p>
</form>
</center>
<?php
}

Now we'll create a function called GetShortUrl for creating the short URL:

function GetShortUrl($url){
 global $conn;
 $query = "SELECT * FROM url_shorten WHERE url = '".$url."' "; 
 $result = $conn->query($query);
 if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
 return $row['short_code'];
} else {
$short_code = generateUniqueID();
$sql = "INSERT INTO url_shorten (url, short_code, hits)
VALUES ('".$url."', '".$short_code."', '0')";
if ($conn->query($sql) === TRUE) {
return $short_code;
} else { 
die("Unknown Error Occured");
}
}
}

The above function is using the generateUniqueID() function to generate a unique id for long urls. We can generate and retrieve the unique ID like so:

function generateUniqueID(){
 global $conn; 
 $token = substr(md5(uniqid(rand(), true)),0,6); $query = "SELECT * FROM url_shorten WHERE short_code = '".$token."' ";
 $result = $conn->query($query); 
 if ($result->num_rows > 0) {
 generateUniqueID();
 } else {
 return $token;
 }
}

Step 4

Now your code is ready to generate a unique short code for long URLs, but we still need to setup the redirection. When redirecting it should also increase a pageview/ hits in the table and then redirect to the longer, original URL.

Here is the code that allows us to do this:

if(isset($_GET['redirect']) && $_GET['redirect']!="")
{ 
$slug=urldecode($_GET['redirect']);
 
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$url= GetRedirectUrl($slug);
$conn->close();
header("location:".$url);
exit;
}

This code uses a function called GetRedirectUrl() that we need to define, like so:

function GetRedirectUrl($slug){
 global $conn;
 $query = "SELECT * FROM url_shorten WHERE short_code = '".addslashes($slug)."' "; 
 $result = $conn->query($query);
 if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
$hits=$row['hits']+1;
$sql = "update url_shorten set hits='".$hits."' where id='".$row['id']."' ";
$conn->query($sql);
return $row['url'];
}
else 
 { 
die("Invalid Link!");
}
}

Step 5

You are almost done now! If you don't want to use the redirect parameter in url for redirection purpose you will need to create a .htaccess file in your project and add following code:

RewriteEngine on
RewriteRule ^([a-z0-9]{6})$ index.php?redirect= [L] 

And You Have done it!

Now if you want to convert any url to short url just pass the url in get param of index.php like following:

http://localhost/myapp/?url=http://www.google.comor http://localhost/myapp/index.php?url=http://www.google.comor just open http://localhost/myappg, '/*')); }); $('.blog_content a').each(function() { $(this).attr('target', '_blank'); }); });