php - 在 WordPress 中搜索自定义表

Question

我目前正在为客户构建一个网站，他们想要一个搜索功能来搜索他们提供的所有产品。

我在数据库中创建了一个名为 sc_products 的新表，然后在 WordPress 模板页面中尝试了以下代码，但似乎没有任何进展。如果有人知道我如何才能让它工作，那就太棒了!

<?php
/* 

Template Name: myTemp

*/

function search_it() {
if ( is_search() && isset($_GET['s'])) {

    global $wpdb;

    $address_table = $wpdb->prefix . "sc_products";
    $myrows = $wpdb->get_results( 'SELECT product FROM ' . $sc_products );
    foreach ( $myrows as $single_row ) {
         global $single_row;
    }
    $product = $single_row->product;
}
    return $product;
}
$city = search_it();
echo $city;

get_search_form();

?>    

Answer 1

这里发生了一些事情，让我们看看是否可以解决它们。

这是您修改后的函数，它应该执行搜索，并带有注释以帮助解释发生了什么。

注释:
1. 您声明 $address_table，但随后使用 $sc_products。这不可能是正确的，因为 $sc_products 未定义。
2. 此答案基于您在评论中提供的列名称 - 如果它们不完全匹配，您需要更新它们，包括匹配的大小写。如果不是 Application，而是 application，您需要进行更改。

function search_it() {
// If this is a search, and the search variable is set
    if ( is_search() && isset( $_GET['s'] ) ) {
        // Global in $wpdb so we can run a query
        global $wpdb;
        // Build the table name - glue the table prefix onto the front of the table name of "sc_products"
        $address_table = $wpdb->prefix . "sc_products";
        // Get the search term into a variable for convenience
        $search = $_GET['s'];
        // To use "LIKE" with the wildcards (%), we have to do some funny business:
        $search = "%{$search}%";
        // Build the where clause using $wpdb->prepare to prevent SQL injection attacks
        // Searching ALL THREE of our columns: Product, Application, Sector 
        $where = $wpdb->prepare( 'WHERE Product LIKE %s OR Application LIKE %s OR Sector LIKE %s', $search, $search, $search );
        // Execute the query with our WHERE clause
        // NOTE: Your code originally used "$sc_products", but that variable was not defined - so have replaced to the proper $address_table here.
        $results = $wpdb->get_results( "SELECT product FROM {$address_table} {$where}" );
        // return the results.  No need to put into an array, it's already an array
        return $product;
    }

    // For consistency, return an empty array if not a search / no search term
    return array();
}

// USAGE:
// Run the query
$cities = search_it();
// You can't echo an array!
// echo $city;
// var_dump to see the whole array
var_dump( $cities );

// More useful USAGE:
$cities = search_it();
// If there's any results
if ( ! empty( $cities ) ) {
    // Output a title
    echo '<h1>Product Search Results:</h1>';
    // Loop over the results
    foreach( $cities AS $city ) {
        // $wpdb returns an array of objects, so output the object properties
        echo '<h2>' . $city->Product . '</h2>';
        echo '<p>' . $city->Application . '</p>';
        echo '<p>' . $city->Sector . '</p>';
    }
}

get_search_form();