Check WordPress path From external database Before Hit Main wordpress Database

Step 1: First need to create DB for URLS

CREATE TABLE url_paths (
id INT AUTO_INCREMENT PRIMARY KEY,
path VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Export All URLs from WordPress

We will extract all the URLs (paths) from WordPress and insert them into the external database.

Here’s a PHP script you can run (in a custom plugin or a standalone PHP file):

<?php


// WordPress database connection (replace with your actual credentials)
$wp_db = new mysqli('localhost', 'wordpress_db', 'password', 'wordpress_db');

// Check connection
if ($wp_db->connect_error) {
    die("Connection failed: " . $wp_db->connect_error);
}

// External database connection
$external_db = new mysqli('localhost', 'urls', 'urls_password', 'urls');

// Check connection
if ($external_db->connect_error) {
    die("Connection failed: " . $external_db->connect_error);
}

// Limit for batch processing
$batch_size = 500; // Process 500 rows at a time
$offset = 0;

// Collect standard posts and pages
do {
    $sql = "SELECT post_name FROM vumyl_posts WHERE post_status = 'publish' AND post_type = 'post' LIMIT $batch_size OFFSET $offset";
    $result = $wp_db->query($sql);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $path = '/' . $row['post_name'] . '/'; // URL path for posts

            // Check if the URL already exists in the external database
            $check_sql = $external_db->prepare("SELECT COUNT(*) FROM url_paths WHERE path = ?");
            $check_sql->bind_param("s", $path);
            $check_sql->execute();
            $check_sql->bind_result($count);
            $check_sql->fetch();
            $check_sql->close();

            if ($count == 0) {
                // Insert into external database if it doesn't exist
                $insert_sql = $external_db->prepare("INSERT IGNORE INTO url_paths (path) VALUES (?)");
                $insert_sql->bind_param("s", $path);
                $insert_sql->execute();
                $insert_sql->close();
            }
        }
    }

    $offset += $batch_size; // Move to the next batch
} while ($result->num_rows > 0);

// Process categories, tags, and taxonomy terms
$taxonomy_sql = "
    (SELECT CONCAT('/category/', slug, '/') AS path FROM wp_terms t INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'category')
    UNION
    (SELECT CONCAT('/tag/', slug, '/') AS path FROM wp_terms t INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'post_tag')
    UNION
    (SELECT CONCAT('/', tt.taxonomy, '/', slug, '/') AS path FROM wp_terms t INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id WHERE tt.taxonomy NOT IN ('category', 'post_tag'))
";

$result = $wp_db->query($taxonomy_sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $path = $row['path'];

        // Check if the URL already exists in the external database
        $check_sql = $external_db->prepare("SELECT COUNT(*) FROM url_paths WHERE path = ?");
        $check_sql->bind_param("s", $path);
        $check_sql->execute();
        $check_sql->bind_result($count);
        $check_sql->fetch();
        $check_sql->close();

        if ($count == 0) {
            // Insert into external database if it doesn't exist
            $insert_sql = $external_db->prepare("INSERT IGNORE INTO url_paths (path) VALUES (?)");
            $insert_sql->bind_param("s", $path);
            $insert_sql->execute();
            $insert_sql->close();
        }
    }
}

// Map custom post types to their respective URL slugs
$custom_post_types = [
    'name' => 'slug',   // Example: 'love' is the post type, 'love-post' is the URL slug
];

// Query to get posts from custom post types
foreach ($custom_post_types as $post_type => $slug) {
    $sql = "SELECT post_name FROM wp_posts WHERE post_status = 'publish' AND post_type = '$post_type'";
    $result = $wp_db->query($sql);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $path = '/' . $slug . '/' . $row['post_name'] . '/'; // URL path for custom post types

            // Check if the URL already exists in the external database
            $check_sql = $external_db->prepare("SELECT COUNT(*) FROM url_paths WHERE path = ?");
            $check_sql->bind_param("s", $path);
            $check_sql->execute();
            $check_sql->bind_result($count);
            $check_sql->fetch();
            $check_sql->close();

            if ($count == 0) {
                // Insert into external database if it doesn't exist
                $insert_sql = $external_db->prepare("INSERT IGNORE INTO url_paths (path) VALUES (?)");
                $insert_sql->bind_param("s", $path);
                $insert_sql->execute();
                $insert_sql->close();
            }
        }
    }
}

echo "All URLs have been added to the external database.";

// Close connections
$wp_db->close();
$external_db->close();
>?

Generated Paths

The script will generate paths for each post_name by prefixing it with a /.
For the example data above, the following paths will be inserted into the external database:

Step3: PHP Code for WordPress

<?php


function check_url_in_external_db_and_route() {
    // Get the requested path (without query parameters)
    $requested_path = strtok($_SERVER['REQUEST_URI'], '?');

    // Connect to the external database using wpdb
    $external_db = new wpdb('urls', 'pass', 'urls', 'localhost');

    // Check for connection error
    if ($external_db->last_error) {
        error_log("Database connection error: " . $external_db->last_error);
        return; // If there's a connection error, allow WordPress to handle the request
    }

    // Query the external database to check if the URL exists
    $exists = $external_db->get_var($external_db->prepare(
        "SELECT COUNT(*) FROM url_paths WHERE path = %s",
        $requested_path
    ));

    if (!$exists) {
        // If URL does not exist, show 404 page
        status_header(404);
        nocache_headers();
        include(get_404_template());
        exit;
    }

    // If URL exists, allow WordPress to handle it normally
}
add_action('template_redirect', 'check_url_in_external_db_and_route');

How It Works

1. Get the Requested Path
   The script retrieves the current path (without the query parameters) using $_SERVER['REQUEST_URI'] and strtok().
2. Connect to the External Database
   A connection to the external database is established using the wpdb class. Make sure the credentials (url, pass, url, localhost) are correct.
3. Check for URL Existence
   The script queries the url_paths table to see if the requested path already exists in the external database.
4. Show 404 if Exists
   If the URL exists, it sets the HTTP status to 404, includes the theme’s 404 template, and exits the script.