Loading imploded arrays into $wpdb prepare failing

2 Views

UPDATE 1

It seems there is an issue with the $addressbits implode. I added in two variables manually, then searched with one to be sure it wouldn’t work, it failed, then entered two values and the search worked.

UPDATE 2

After more testing I discovered that the prepare function eliminates the second implode if it has more than one term in the array, not sure if this is a bug but it sure is annoying. I’m looking into running the prepare function inside the array creating function to sanitize, then just print it out to the query.

Read More

I’m building a search form for a custom table in a wordpress site. I’m trying to pass multiple terms from text fields to an array, then pass them into $wpdb->prepare() for sanitizing. So far my code works correctly for exactly one term… but if I add in a second, it won’t work, it’s as if no terms were entered for the field. I initially thought it might be an issue with trailing commas but adding code to trim didn’t work, I even concatenated in commas to be sure of their presence, then trimmed with rtrim(), still wouldn’t work. I also added in just commas and not trim, still fails with multiple terms. I have error reporting turned on, nothing is showing up. 

#explode search field values
$addressinput = explode(" ", $_GET['address']);

#create inputs for prepare
$addressbits = array();
foreach ($addressinput as $input) {
    $input = trim($input);
    if (!empty($input)) {
        $addressbits[] = $input ;
    }
}

#create prepare statements
$addressclean = array();
foreach ($addressinput as $clean) {
    $clean = trim($clean);
    if (!empty($clean)) {
        $addressclean[] = " and `display-address` LIKE '%%%s%%'";
    }
}


#if field is not empty, build and execute prepare statement, set variable
if (isset($_GET['address'])) {  
   $displayaddress = $wpdb->prepare( implode( $addressclean ) , implode(',' , $addressbits));

   }  else {

    $displayaddress = '';
  }

#query with pagination code

$customPagHTML     = "";
$query             = "select * from `record-display` where" . $code . $displayaddress . $format  . $name . " ORDER BY `date` DESC";
$total_query     = "SELECT COUNT(1) FROM (${query}) AS combined_table";
$total             = $wpdb->get_var( $total_query );
$items_per_page = 10;
$page             = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
$offset         = ( $page * $items_per_page ) - $items_per_page;
$result         = $wpdb->get_results( $query . "  LIMIT ${offset}, ${items_per_page}", ARRAY_A );
$totalPage         = ceil($total / $items_per_page);
Post Views: 2

Related posts

1 comment

  1. So you seem to have a few issues.

    First, there’s no point in having all of the code for assembling $adressclean run if there’s no $_GET['address'] – it won’t make a difference(performance wise), but it would make more sense to move it further down inside of the if ( isset( $_GET['address'] ) ) {.

    The second thing is that you’re looping over pretty much the same array twice. Again – not really a big deal, but unless you actually need the $addressbits variable, then just make a single loop(you can actually still do a single loop even if you need it, just do $addressbits[] = $input;).

    The third thing is that you’re using $wpdb->prepare() incorrectly. It expects that you pass each replacement variable as another parameter to the function. Instead you are concatenating all $addressbits and passing them as a single variable, while concatenating $addressclean would require multiple parameters being passed to $wpdb->prepare().
    It’s easier to escape each fragment inside of the foreach loop.
    If you don’t want to do that, then you can write the code like you initially did, just update these lines(they are taken out of context, but you should be able to figure it out):

    $addressbits[] = '%' . $wpdb->esc_like( $input ) . '%';

$addressclean[] = " and `display-address` LIKE %s";

$prepare_params = $addressbits;
array_unshift( $prepare_params, implode( $addressclean ) ); 
$displayaddress = call_user_func_array( array( $wpdb, 'prepare', implode( $addressclean ), $addressbits );

    This will add percentages to all $addressbits elements, making it ready for use in a LIKE statement. As you can see we got rid of the single quotes and all extra percentages in $addressclean[].
    In the end we used call_user_func_array() to pass an array of parameters to $wpdb->prepare(). Note that it actually receives separate parameters, and not a single array(check out the documentation on call_user_func_array() if necessary).

    The last thing is that you don’t need to run a full extra query to get the total number of results. Use SQL_CALC_FOUND_ROWS instead – although depending on your table structure it might be better to run a second query instead(try both approaches and see which one runs faster for you).

    In any case, here’s my suggested version of your code. Note that I only tried it by querying the posts table based on post_title. And I had to make up $code, $format and $name since you didn’t include them in your code.

    #if field is not empty, build and execute prepare statement, set variable
if ( isset( $_GET['address'] ) ) {
    #explode search field values
    $addressinput = explode( ' ', $_GET['address'] );
    #create inputs for prepare
    $addressclean = array();
    foreach ( $addressinput as $input ) {
        $input = trim( $input );
        if ( ! empty( $input ) ) {
            $addressclean[] = $wpdb->prepare( '`display-address` LIKE %s', '%' . $wpdb->esc_like( $input ) . '%' );
        }
    }

    $displayaddress = $addressclean ? ' AND ( ' . implode( ' AND ', $addressclean ) . ' ) ' : '';
}  else {
    $displayaddress = '';
}

#query with pagination code
$items_per_page = 10;
$page           = isset( $_GET['cpage'] ) ? absint( $_GET['cpage'] ) : 1;
$offset         = ( $page * $items_per_page ) - $items_per_page;

$customPagHTML  = "";
$query          = "SELECT SQL_CALC_FOUND_ROWS * FROM `record-display` 
    WHERE $code
        $displayaddress
        $format
        $name
    GROUP BY {$wpdb->posts}.ID
    ORDER BY `post_date` DESC
    LIMIT {$offset}, {$items_per_page}";
$result      = $wpdb->get_results( $query, ARRAY_A );
$total       = $wpdb->get_var( "SELECT FOUND_ROWS()" );
$totalPage   = ceil( $total / $items_per_page );

Comments are closed.