SQL URL Replacer – From Local to Live

Migrating a website from a local environment to a live server.

A simple SQL URL Replacer tool using HTML, CSS, and JavaScript. This tool helps generate SQL queries that can update all references of the old URL to the new URL in a WordPress database. Vide Finished Code: sql.html

Building the SQL URL Replacer Tool

The SQL URL Replacer tool is designed to be user-friendly and efficient, requiring only the old (local) and new (remote) URLs as inputs to generate the necessary SQL update commands.

HTML Structure

The user interface is straightforward, consisting of input fields for the local and remote URLs and a button to generate the SQL commands. Here’s the HTML setup:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL URL Replacer</title>
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@100;200;300;500;600&display=swap" rel="stylesheet">
</head>
<body>
    <div style="padding: 5%">
        <h2>SQL URL Replacer</h2>
        <div>
            <label for="localURL">Current URL:</label>
            <input type="text" id="localURL" placeholder="https://local:8888/" style="padding: 4px 12px; border: 1px solid #dadada; border-radius: 6px">
        </div>
        <div style="padding-top: 10px">
            <label for="remoteURL">New URL:</label>
            <input type="text" id="remoteURL" placeholder="https://www.remote.com.au/" style="padding: 4px 12px; border: 1px solid #dadada; border-radius: 6px">
        </div>
        <br>
        <button onclick="generateSQL()">Generate SQL</button>
        <h3>Generated SQL:</h3>
        <textarea id="output" rows="10" cols="70" readonly style="padding: 4px 12px; border: 1px solid #dadada; border-radius: 6px"></textarea>
    </div>
</body>
</html>

CSS Styling

For this tool, we’re using Google’s Poppins font to keep the UI clean and modern. The styling is minimal, focusing on readability and usability without any unnecessary distractions.

JavaScript Functionality

The JavaScript function generateSQL captures the user input from the text fields, constructs several SQL statements to update different parts of the WordPress database, and displays these statements in a textarea for easy copying and pasting:

function generateSQL() {
    const localURL = document.getElementById('localURL').value;
    const remoteURL = document.getElementById('remoteURL').value;

    let sql = `
-- Update wp_options table
UPDATE wp_options 
SET option_value = REPLACE(option_value, '${localURL}', '${remoteURL}') 
WHERE option_name = 'home' OR option_name = 'siteurl';

-- Update wp_posts table
UPDATE wp_posts 
SET guid = REPLACE(guid, '${localURL}', '${remoteURL}');
UPDATE wp_posts 
SET post_content = REPLACE(post_content, '${localURL}', '${remoteURL}');

-- Update wp_postmeta table
UPDATE wp_postmeta 
SET meta_value = REPLACE(meta_value, '${localURL}', '${remoteURL}');
    `;

    document.getElementById('output').value = sql;
}

Conclusion

The SQL URL Replacer tool simplifies one of the more tedious aspects of website migration. By automating the generation of SQL update statements, developers can ensure a smoother transition from development to production environments, reducing the risk of errors and saving time.