How To Build A URL Shortener (PHP, MySQL, .htaccess)

With so many long URLs cluttering the web, having your own URL shortener can help create clean, short links perfect for marketing campaigns, social media, or personal projects. From a marketing standpoint, URL shorteners are critical for a couple of reasons:

• Campaign Tracking: You should always append your Analytics UTM parameters to track where every visit is coming from. However, those parameters often take a typical URL, making it long and unmanageable.
• QR Codes: Throw a long URL in a QR Code, and you get a huge QR Code. Shortened URLs make QR Codes far more simple, which can extend their visibility from further away.

Because of this, you may be tempted to buy a license for a URL shortener… but you’d be amazed at how simple they are to build. In this step-by-step guide, you’ll learn how to create a URL shortener using PHP, MySQL, and .htaccess for clean, shortened URLs like http://yourdomain.com/abc123.

In this guide, we’ll walk through how to build a URL shortener using PHP, MySQL, and .htaccess, and we’ll incorporate critical security enhancements, database optimizations, and performance improvements. We will cover how to:

• Use PHP to handle URL shortening and redirection.
• Set up a MySQL database with optimized structure and security features.
• Implement .htaccess for clean and user-friendly shortened URLs.
• Enhance the entire system’s security, prevent URL guessing, and improve performance.

Step 1: Set Up a Secure MySQL Database

A robust database structure is crucial for efficient URL storage and redirection. Follow these steps to set up your MySQL database securely.

1. Create a MySQL database (if you don’t have one already) and create a user with limited privileges to minimize security risks:

CREATE USER 'url_shortener_user'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT INSERT, SELECT, UPDATE ON your_database.urls TO 'url_shortener_user'@'localhost';

2. Create the URLs table:

CREATE TABLE urls (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    original_url TEXT NOT NULL,
    short_code VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    clicks INT DEFAULT 0,
    last_visited TIMESTAMP NULL,
    expiry_date TIMESTAMP NULL
);

• id: A unique identifier for each URL.
• original_url: Stores the original URL.
• short_code: The shortened code.
• clicks: Tracks how many times the URL was accessed.
• last_visited: The last time the short URL was clicked.
• expiry_date: Optionally sets when the URL will expire.
• Indexes: Add an index to the short_code column to optimize lookups:

CREATE INDEX idx_short_code ON urls (short_code);

This makes retrieving URLs faster, especially as the table grows.

Step 2: Create the PHP Script with Security and Performance Features

Create a shortener.php file that:

1. Generates short codes.
2. Stores the URLs securely in the database.
3. Redirects users when they visit a short URL.
4. Tracks clicks and handles expired URLs.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Could not connect to the database: " . $e->getMessage());
}

// Validate the URL before storing it
function validateURL($url) {
    return filter_var($url, FILTER_VALIDATE_URL) && strlen($url) <= 2048;
}

// Generate a unique short code
function generateUniqueShortCode($pdo, $length = 6) {
    do {
        $shortCode = generateShortCode($length);
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM urls WHERE short_code = :code");
        $stmt->execute(['code' => $shortCode]);
    } while ($stmt->fetchColumn() > 0);

    return $shortCode;
}

// Generate a random short code
function generateShortCode($length = 6) {
    return substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, $length);
}

// Shorten a URL and return the short code
function shortenURL($pdo, $url) {
    if (!validateURL($url)) {
        die("Invalid URL provided.");
    }

    // Check if the URL already exists
    $stmt = $pdo->prepare("SELECT short_code FROM urls WHERE original_url = :url LIMIT 1");
    $stmt->execute(['url' => $url]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        return $result['short_code'];
    }

    // Generate a unique short code and store the URL
    $shortCode = generateUniqueShortCode($pdo);
    $stmt = $pdo->prepare("INSERT INTO urls (original_url, short_code) VALUES (:url, :code)");
    $stmt->execute(['url' => $url, 'code' => $shortCode]);

    return $shortCode;
}

// Handle URL redirection, clicks, and expiry
if (isset($_GET['code'])) {
    $shortCode = $_GET['code'];

    // Find the original URL
    $stmt = $pdo->prepare("SELECT original_url, clicks, expiry_date FROM urls WHERE short_code = :code LIMIT 1");
    $stmt->execute(['code' => $shortCode]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        // Check if the URL has expired
        if ($result['expiry_date'] && strtotime($result['expiry_date']) < time()) {
            die("This URL has expired.");
        }

        // Update the click count and last visited time
        $stmt = $pdo->prepare("UPDATE urls SET clicks = clicks + 1, last_visited = NOW() WHERE short_code = :code");
        $stmt->execute(['code' => $shortCode]);

        // Redirect to the original URL
        header("Location: " . $result['original_url']);
        exit();
    } else {
        die("URL not found.");
    }
}

// Handle URL shortening form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST' && !empty($_POST['url'])) {
    $url = $_POST['url'];
    $shortCode = shortenURL($pdo, $url);
    $shortURL = "http://yourdomain.com/" . $shortCode;
    echo "Shortened URL: $shortURL";
}
?>

Key Security Enhancements in the Code:

1. URL Validation: Ensures that only valid URLs are shortened.
2. Rate Limiting & reCAPTCHA (optional): To prevent abuse, implement rate limiting using tools like mod_evasive or use Google reCAPTCHA in the form.
3. HTTPS Enforcement: Ensure that HTTPS is enforced using .htaccess.

Step 3: Use .htaccess to Create Clean URLs

To rewrite the URLs so that users only see the shortened code (http://yourdomain.com/abc123), create an .htaccess file in the root directory:

RewriteEngine On
RewriteCond %{HTTPS} !=on
RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

RewriteRule ^([a-zA-Z0-9]{6})$ shortener.php?code=$1 [L]

• HTTPS Redirection: Ensures all traffic is encrypted by redirecting HTTP requests to HTTPS.
• URL Rewriting: Converts any 6-character alphanumeric short code into a parameter for shortener.php.

Step 4: Enhance Performance with Caching and Optimization

1. Browser Caching: Add headers to cache the redirect in the user’s browser for a set time:

header("Cache-Control: max-age=3600"); // Cache for 1 hour

2. Database Indexing: Ensure the short_code column is indexed for faster lookups.
3. Track Clicks: Update the click count and last_visited time every time a URL is accessed.

$stmt = $pdo->prepare("UPDATE urls SET clicks = clicks + 1, last_visited = NOW() WHERE short_code = :code");
$stmt->execute(['code' => $shortCode]);

4. Optional Caching with Memcached or Redis: Cache frequently accessed short URLs to minimize database hits.

Step 5: Track and Manage Expiring URLs

To add an expiration feature, ensure the expiry_date column is used to set URL expiration dates. Check whether the URL has expired during redirection:

if ($result['expiry_date'] && strtotime($result['expiry_date']) < time()) {
    die("This URL has expired.");
}

How Many URLs Can I Store?

The number of URLs you can store in this URL shortener depends primarily on two factors:

1. The Length of the Short Code: In the example provided, the short code is six characters long and uses a combination of 62 characters (uppercase A-Z, lowercase a-z, and digits 0-9). Using the formula for possible combinations: [

2. With a 6-character code, you can theoretically store 56.8 billion unique URLs.
3. The other limiting factor is database size. Each URL takes up space in your MySQL database. Since URLs can vary in length, the total storage space needed depends on the length of the original URLs.
   • The short code itself (6 characters) will take negligible space.
   • The original URL stored as TEXT can hold up to 65,535 bytes (around 65 KB), which is sufficient for almost any URL.
   • The database size limit depends on your MySQL configuration and hosting environment. For example, MySQL databases can handle tables up to several terabytes, depending on the storage engine and file system.
   • If you have URLs that average 100 bytes in length (about 100 characters per URL), and you want to store 1 million URLs, you would need about 100 MB of space (not counting database overhead). If your database has 1 GB of free storage, you could store roughly 10 million URLs, assuming an average URL length of 100 characters.

With a 6-character code, you could theoretically store up to 56.8 billion unique URLs, far exceeding what most URL shorteners need. Your database’s storage capacity would likely constrain the actual limit. Do you need more or less capacity?

• Shorter Codes: If you use shorter codes, like 5 characters, the total combinations would drop to ( 62^5 = 916,132,832 ), limiting you to around 916 million URLs.
• Longer Codes: If you use longer short codes (e.g., 7 or 8 characters), the number of possible combinations increases dramatically:
  • 7 characters: ( 62^7 = 3.52 times 10^{10} ) (~35 trillion URLs)
  • 8 characters: ( 62^8 = 218.34 times 10^{12} ) (~218 trillion URLs)

You’ve built a secure, optimized, scalable URL shortener that can withstand attacks, handle large-scale usage, and provide a clean user experience. With HTTPS enforcement, rate limiting, click tracking, and expiring URLs, this shortener is ready for real-world deployment.