Part 2: Enhancing Your WordPress Plugin to Search a MySQL Database

In the previous post, we created a basic WordPress plugin with a shortcode and an admin settings page. Now, let’s take it a step further by adding functionality to search a MySQL database. We’ll call this new functionality “Search MySQL.”

Step 1: Setting Up Your Database

For this tutorial, let’s assume you have a MySQL table named wp_sample_data with the following structure:

sqlCopy codeCREATE TABLE wp_sample_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Populate the table with some sample data:

sqlCopy codeINSERT INTO wp_sample_data (name, email) VALUES 
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Alice Johnson', '[email protected]');

Step 2: Adding the Search Form Shortcode

First, we’ll create a shortcode that displays a search form.

1. Create the Shortcode: Add the following code to your my-first-plugin.php file to create the search form.

phpCopy code// my-first-plugin.php

// Function to display the search form
function my_first_plugin_search_form() {
    ob_start();
    ?>
    <form method="post" action="">
        <label for="search_term">Search:</label>
        <input type="text" id="search_term" name="search_term" required>
        <input type="submit" value="Search">
    </form>
    <?php
    return ob_get_clean();
}

// Register the search form shortcode
function my_first_plugin_register_search_shortcode() {
    add_shortcode('search_form', 'my_first_plugin_search_form');
}
add_action('init', 'my_first_plugin_register_search_shortcode');

2. Displaying the Search Form: Use the [search_form] shortcode in a post or page to display the search form.

Step 3: Handling the Search Request

Next, we’ll handle the search request and display the results.

1. Search Functionality: Add the following code to handle the search request.

phpCopy code// my-first-plugin.php

// Function to handle the search request
function my_first_plugin_handle_search() {
    if ($_SERVER['REQUEST_METHOD'] == 'POST' && !empty($_POST['search_term'])) {
        global $wpdb;
        $search_term = sanitize_text_field($_POST['search_term']);
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM wp_sample_data WHERE name LIKE %s OR email LIKE %s", 
            '%' . $wpdb->esc_like($search_term) . '%', 
            '%' . $wpdb->esc_like($search_term) . '%'
        ));

        if ($results) {
            echo '<h2>Search Results:</h2>';
            echo '<ul>';
            foreach ($results as $result) {
                echo '<li>' . esc_html($result->name) . ' - ' . esc_html($result->email) . '</li>';
            }
            echo '</ul>';
        } else {
            echo '<p>No results found for "' . esc_html($search_term) . '".</p>';
        }
    }
}
add_action('wp_footer', 'my_first_plugin_handle_search');

Step 4: Displaying Search Results

Now, let’s integrate the search results display with the search form shortcode.

1. Update Search Form Shortcode: Modify the search form shortcode to include the results display.

phpCopy code// my-first-plugin.php

// Function to display the search form and handle search results
function my_first_plugin_search_form() {
    ob_start();
    ?>
    <form method="post" action="">
        <label for="search_term">Search:</label>
        <input type="text" id="search_term" name="search_term" required>
        <input type="submit" value="Search">
    </form>
    <?php
    if ($_SERVER['REQUEST_METHOD'] == 'POST' && !empty($_POST['search_term'])) {
        global $wpdb;
        $search_term = sanitize_text_field($_POST['search_term']);
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM wp_sample_data WHERE name LIKE %s OR email LIKE %s", 
            '%' . $wpdb->esc_like($search_term) . '%', 
            '%' . $wpdb->esc_like($search_term) . '%'
        ));

        if ($results) {
            echo '<h2>Search Results:</h2>';
            echo '<ul>';
            foreach ($results as $result) {
                echo '<li>' . esc_html($result->name) . ' - ' . esc_html($result->email) . '</li>';
            }
            echo '</ul>';
        } else {
            echo '<p>No results found for "' . esc_html($search_term) . '".</p>';
        }
    }
    return ob_get_clean();
}

Conclusion

You’ve now enhanced your WordPress plugin to include a search form that queries a MySQL database and displays the results. This addition makes your plugin more interactive and demonstrates how to handle form submissions and database queries within a WordPress environment.

Happy coding!