SPONSORS

3456 views · 141 days ago

![Creating a URL shortener application in PHP & MySQL](https://images.ctfassets.net/vzl5fkwyme3u/3OjKfmylv2k8eWayqeCUWG/6d8e996fc7c757135df7fdd322fd50a4/AdobeStock_65694631.jpeg?w=1000)

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

```mysql

CREATE TABLE IF NOT EXISTS `url_shorten` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`url` tinytext NOT NULL,

`short_code` varchar(50) NOT NULL,

`hits` int(11) NOT NULL,

`added_date` 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

```php

$servername = 'localhost';

$username = 'root';

$password = ''; / /on localhost by default there is no password

$dbname = 'shorten_db';

$base_url='http://localhost/myapp/'; / / it is your application url

```

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

```php

if(isset($_GET['url']) && $_GET['url']!="")

{

$url=urldecode($_GET['url']);

if (filter_var($url, FILTER_VALIDATE_URL))

{

/ / Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

/ / Check connection

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

{ / /show the basic html form having element url

?>

<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:

```php

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:

```php

function generateUniqueID(){

global $conn;

$token = substr(md5(uniqid(rand(), true)),0,6); / / creates a 6 digit unique short id

$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:

```php

if(isset($_GET['redirect']) && $_GET['redirect']!="")

{

$slug=urldecode($_GET['redirect']);

/ / Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

/ / Check connection

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:

```php

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();

/ / increase the hit

$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:

```php

RewriteEngine on

RewriteRule ^([a-z0-9]{6})$ index.php?redirect=$1 [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.com **or** http://localhost/myapp/index.php?url=http://www.google.com **or** just open http://localhost/myapp/ **or** http://localhost/myapp/index.php,

It will show a html form where you may input any URL and click submit.

After submitting form or hitting the url with param url then it will print a shortened URL on screen. You may use that URL in new tab and see the url will be redirect to long URL and increase a Hit/ Pageview in MySQL table.

## Please find the full code for index.php here:

```php

<?php

$servername = 'localhost';

$username = 'root';

$password = ''; / / on localhost by default there is no password

$dbname = 'shorten_db';

$base_url='http://localhost/myapp/'; / / it is your application url

if(isset($_GET['url']) && $_GET['url']!="")

{

$url=urldecode($_GET['url']);

if (filter_var($url, FILTER_VALIDATE_URL))

{

/ / Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

/ / Check connection

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

}

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");

}

}

}

function generateUniqueID(){

global $conn;

$token = substr(md5(uniqid(rand(), true)),0,6); / / creates a 6 digit unique short id

$query = "SELECT * FROM url_shorten WHERE short_code = '".$token."' ";

$result = $conn->query($query);

if ($result->num_rows > 0) {

generateUniqueID();

} else {

return $token;

}

}

if(isset($_GET['redirect']) && $_GET['redirect']!="")

{

$slug=urldecode($_GET['redirect']);

/ / Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

/ / Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$url= GetRedirectUrl($slug);

$conn->close();

header("location:".$url);

exit;

}

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();

/ / increase the hit

$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!");

}

}

```

SPONSORS