Searching a custom WP table and displaying results in an HTML table

Question

I have a custom table in the WP database called wp_products. It has columns titled ID, SKU, Details and Page. I'm trying to search this table via the default search box, so I've replaced my search.php file with the code below. This isn't working and I don't know enough PHP to fix it. Can anyone help me out with this?

<?php
/**
 * Author:          
 * Created on:      
 *
 * @package Neve
 */

function search_it() {
    if ( is_search() && isset( $_GET['s'] ) ) {
        global $wpdb;
        $address_table = $wpdb->prefix . "wp_products";
        $search = $_GET['s'];
        $search = "%{$search}%";
        $where = $wpdb->prepare( 'WHERE SKU LIKE %s OR Details LIKE %s OR Page LIKE %s', $search, $search, $search );
        $results = $wpdb->get_results( "SELECT * FROM {$address_table} {$where}" );
        return $product;
    }
    return array();
}

$container_class = apply_filters( 'neve_container_class_filter', 'container', 'blog-archive' );

get_header();

$cities = search_it();
if ( ! empty( $cities ) ) {
    echo '<h1>Catalogue Search Results:</h1>';
    foreach( $cities AS $city ) {
            echo "<table width='100%' align='center' border='3px solid grey'>";
            echo "<tr>";
            echo "<th style='background: #B9C9FE;'>Part Number</th>";
            echo "<th style='background: #B9C9FE;'>Description</th>";
            echo "<th style='background: #B9C9FE;'>Page</th>";
            echo "</tr>";
            echo "<tbody>";


            for ($i=$start;$i < $end ;++$i ) {
            $results = $results[$i];

            echo "<tr>";
            echo "<td>$city->SKU</td>";
            echo "<td>$city->description</td>";
            echo "<td>$city->Page</td>";
            echo "</tr>";
            }
            echo "</tbody>";
            echo "</table>"; 
    }
}

get_search_form();

?>

<?php
get_footer();

Here's the updated code:

<?php
/*
Template Name: Search Page
*/
function search_it() {

    $search = filter_input( INPUT_GET, 's', FILTER_SANITIZE_STRING );

    if ( ! is_search() || empty( $search ) ) {
        return array();
    }

    global $wpdb;

    $address_table = $wpdb->prefix . 'products';

    $search = $wpdb->esc_like( $search );
    $search = "%{$search}%";

    $query = "SELECT * FROM {$wpdb->prefix}products WHERE SKU LIKE %s OR Details LIKE %s OR Page Like %s";
    $query = $wpdb->prepare( $query, $search, $search, $search );
    return $wpdb->get_results();
}

get_header();

$cities = search_it();
if ( ! empty( $cities ) ) {
    echo '<h1>Catalogue Search Results:</h1>';
    echo "<table width='100%' align='center' border='3px solid grey'>";
    echo "<tr>";
    echo "<th style='background: #B9C9FE;'>Part Number</th>";
    echo "<th style='background: #B9C9FE;'>Description</th>";
    echo "<th style='background: #B9C9FE;'>Page</th>";
    echo "</tr>";
    echo "<tbody>";


    foreach( $cities AS $city ) {
        echo "<tr>";
        echo "<td>{$city->SKU}</td>";
        echo "<td>{$city->Details}</td>";
        echo "<td>{$city->Page}</td>";
        echo "</tr>";
    }

    echo "</tbody>";
    echo "</table>"; 
} else {
    echo "<h1 class='page-title'>";
    printf( __( 'There are no search results for: %s', 'shape' ), '<span>' . get_search_query() . '</span>' );
    echo ".</h1>";
    $query;
}

get_search_form();

get_footer();

Here's the print_r( $wpdb->prepare( $query, $search, $search, $search ) ); die; output:

SELECT * FROM wp_products WHERE SKU LIKE '{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}GASKET{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}' OR Details LIKE '{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}GASKET{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}' OR Page Like '{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}GASKET{feeb191b44038789f6b0c639a9f2fe6ff48090427c92513c8582b33a4cbf1c42}'

The above is the output I get when I run this query:

$query = "SELECT * FROM {$wpdb->prefix}products WHERE SKU LIKE %s OR Details LIKE %s OR Page Like %s";

Answer 1

There's a lot going on in the code, so I rewrote your main function with some comments. Overview of notes:

$address_table is superfluous, and also probably not what you wanted

You only use this variable once, so it's safe to just in-line it in your query. Also, your use of $wpdb->prefix expands $address_table to the value "wp_wp_products", probably not what you want. I left it in the code with some notes, but you don't need any of that in your final method, just use what's in the $query.

Early Return

Returning when your conditions fail, instead of doing logic when they pass (the is_search() && isset( $_GET['s'] ) block), makes your code more readable.

Escaping input

I know you were using $wpdb->prepare eventually, but it doesn't hurt to sanitize input as soon as you plan to use it. This way, if you pass $search somewhere else later, it will hopefully be safer than it was when it came to your function. Also note the use of $wpdb->esc_like() to prepare your search for use in a LIKE clause.

function search_it() {

    // Sanitize user input early, especially if assigning to a variable like later.
    $search = filter_input( INPUT_GET, 's', FILTER_SANITIZE_STRING );

    // Early returns make your code more readable.
    if ( ! is_search() || empty( $search ) ) {
        return array();
    }

    global $wpdb;

    /**
     * Some things about `$address_table`:
     * - You don't need double quotes if you're concatenating
     * - You only use it in your query, see below where I incorporate it inline.
     * - $wpdb->prefix is usually "wp_", so you're table comes out to "wp_wp_products" - 
     * I don't think that's what you want.
     */
    $address_table = $wpdb->prefix . 'products';

    // Double quote would look like this: 
    // $address_table = "{$wpdb->prefix}products";

    // https://codex.wordpress.org/Class_Reference/wpdb/esc_like
    $search = $wpdb->esc_like( $search );
    $search = "%{$search}%";

    // Why not prepare the whole query"?
    // Also, you don't even need $address_table
    $query = "SELECT * FROM {$wpdb->prefix}products WHERE SKU LIKE %s OR Details LIKE %s OR Page Like %s";
    $query = $wpdb->prepare( $query, $search, $search, $search );
    return $wpdb->get_results();
}

Please let me know if you have any questions.

Edit: I accidentally typed filter_validate instead of filter_input

To address the second part of your code, it looks like the inner for loop is using $start and $end, which aren't defined. You also don't need another loop since you're already looping your results. Try this instead:

$cities = search_it();
if ( ! empty( $cities ) ) {
    echo '<h1>Catalogue Search Results:</h1>';
    echo "<table width='100%' align='center' border='3px solid grey'>";
    echo "<tr>";
    echo "<th style='background: #B9C9FE;'>Part Number</th>";
    echo "<th style='background: #B9C9FE;'>Description</th>";
    echo "<th style='background: #B9C9FE;'>Page</th>";
    echo "</tr>";
    echo "<tbody>";


    foreach( $cities AS $city ) {
        echo "<tr>";
        echo "<td>{$city->SKU}</td>";
        echo "<td>{$city->description}</td>";
        echo "<td>{$city->Page}</td>";
        echo "</tr>";
    }

    echo "</tbody>";
    echo "</table>"; 
}

Answer 2

I didn't look very deep into your code, but in your search_it() function you return $product instead of $results. $product is nowhere, it is always empty. You should activate WP_DEBUG in your wp-config.php, there must be errors from PHP.