1

I need to program a page to which I pass an initial through the url and it will show all author users whose last name starts with that letter. I would like to do this via WP User Query so that I can easily loop over the results

Right now I have this:

<?php $letter = $_GET['p'] ?>
<?php
  $sql = "SELECT umfirst.meta_value AS first_name, umlast.meta_value AS last_name
          FROM $wpdb->usermeta umfirst, $wpdb->usermeta umlast, $wpdb->users user
          WHERE umlast.meta_key = 'last_name'
          AND umfirst.meta_key = 'first_name'
          AND umfirst.user_id = umlast.user_id
          AND user.id = umfirst.user_id
          AND umlast.meta_value LIKE '".$letter."%'";

  $rows = $wpdb->get_results($sql);
  foreach ($rows as $row) {
    // Do stuff with the results
  }
?>

However I would prefer to user WP Query since it's easier to add other constraints, but I can't get this one to work with WP Query umlast.meta_value LIKE '".$letter."%'"

3

The problem is that you can't use the value for the LIKE comparison as you've mentioned. The generated SQL will look something along the lines of:

AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%B%'

which means that the query is set to look for every occurrence of letter B in our case. Of course this is not what you want, what you would need is something like this:

AND CAST(wp_usermeta.meta_value AS CHAR) LIKE 'B%'

so that the search will be done only starting with the B letter.

The good news is that you can still achieve what you need but with the cost of looking a bit like a hack. :)

The WP_User_Query:

$args = array(
    'meta_key'      => 'last_name',
    'meta_value'    => 'B########', // The '########' sequence acts sort of like an unique identifier
    'meta_compare'  => 'LIKE',
);

$wt_user_query = new WP_User_Query( $args );

And in functions.php we hook to the get_meta_sql filter and alter the WHERE clause:

function wt_custom_meta_sql( $sql ) {
    /* 
        Search for that funky so called unique identifier
        that we used to initialize our `WP_User_Query' object    
    */
    $uid = '########';
    preg_match( "/'(%[^']+{$uid}%)'/", $sql['where'], $matches );

    if ( ! empty( $matches ) ) {
        /* 
            We've found it and now we get rid of the
            extra '%' character as well as our identifier               
        */
        $val = str_replace( "{$uid}%", "%", ltrim( $matches[1], '%' ) );
        $sql['where'] = str_replace( $matches[0], "'{$val}'", $sql['where'] );
    }

    return $sql;
}
add_filter( 'get_meta_sql' , 'wt_custom_meta_sql', 40 );
  • @JuanFuentes Also, never build your queries like you did with data (your $letter variable) taken directly from input without any filtering. This is the premise of an SQL injection attack. – webtoure Aug 14 '15 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.