WordPress: wpdb prepare with conditional variable

6 Views

I currently have a table. If the user searches for something, I would like the query to return the filtered results. If the user doesn’t search for something, it should return all results. I’m not too sure how to do this with wpdb prepare. 

if($search_query!=="all") {
    $search_query = '%' . $search_query . '%';
    $where = 'WHERE column_name LIKE %s';   
}

$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ".$where." ORDER BY id DESC LIMIT %d, %d", $search_query,$current_page,$rows_per_page));

Right now nothing returns when the search field is empty because the query is erroring out because it’s throwing the parametrization off and passing $search_query to the %d beside LIMIT. Is it possible to make this variable conditional? Is there a way to do this without an IF statement ?

Post Views: 6

Related posts

3 comments

  1. It looks like you can pass an array to prepare, as well as a list of variables, according to the WordPress documentation

    That means that you could do something like this:

    $where = "";

$parameters = array($search_query,$current_page,$rows_per_page);

if($search_query!=="all") {
    array_push($parameters, '%' . $search_query . '%');
    $where = 'WHERE column_name LIKE %s';   
}

$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ".$where." ORDER BY id DESC LIMIT %d, %d", $parameters));

    Your WHERE clause will be empty if there’s no data, so concatenating it into your query won’t cause issues.

  2. Why not do the prepare in the “If” statement? You can then do the other prepare (without the where clause) in the “Else” and just use the get_results on the proper prepared query?

    if($search_query!=="all") {
    $search_query = '%' . $search_query . '%';
    $where = 'WHERE column_name LIKE %s'; 
    $prepared = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ".$where." ORDER BY id DESC LIMIT %d, %d", $search_query, $current_page, $rows_per_page)  ;
} else {
    $prepared = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ORDER BY id DESC LIMIT %d, %d", $current_page, $rows_per_page);
}
$results = $wpdb->get_results($prepared);

  3. You can escape the like parameter yourself and add it as a where clause if needed like this:

    function like($str)
{
    global $wpdb;
    return "'" . '%' . esc_sql($wpdb->esc_like($str)) . '%' . "'";
}

if($search_query!=="all") {
    $where = 'WHERE column_name LIKE ' . like($search_query);   
}

    and remove the search_query param from the prepared statement

Comments are closed.