0

I am trying to view a list of users (who are music artists) with the following function.

function show_authors($ltr) {
    global $wpdb;
    $querystr = "SELECT $wpdb->users.* FROM $wpdb->users WHERE $wpdb->users.display_name LIKE '$ltr%' ORDER BY $wpdb->users.display_name";

/**
    $querystr = "
    SELECT      *
    FROM        $wpdb->users
    INNER JOIN  $wpdb->usermeta.meta_value
                ON $wpdb->users.user_id = $wpdb->usermeta.user_id
                AND $wpdb->usermeta.meta_key = 'wp_capabilities'
                AND $wpdb->usermeta.meta_value = %artist%
    WHERE       $wpdb->users.display_name LIKE '$ltr%' 
            AND $wpdb->usermeta.meta_key = 'wp_capabilities' 
                    AND $wpdb->usermeta.meta_value LIKE '%artist%'
    ORDER BY    $wpdb->users.display_name ASC
    ";
**/

    $users = $wpdb->get_col($querystr);
    echo "<ul style=\"list-style-type: none;\">";
        foreach($users as $user) {
          $user = get_userdata($user);
          $post_count = get_usernumposts($user->ID);
           echo '<li><i class="fa fa-user"></i> <a href="' . get_author_posts_url($user->ID, $user->user_nicename) . '"> ' . $user->first_name . ' ' . $user->last_name . '</a> ('. $post_count .' songs)</li>';
    }
    echo "</ul>";
}

I tried inner join the usermeta table so that I can select the necessary users with the meta_key named wp_capabilities which has the name of the user role in the meta_value field.

The shown code works fine. When I try to use the commented out query, the result is empty. What I might be doing wrong? Or is there any efficient or better way to achieve this?

1 Answer 1

3

The Problems

You risk SQL injection, with your current setup:

$wpdb->users.display_name LIKE '$ltr%'

The following could also be a problematic part in your SQL query:

AND $wpdb->usermeta.meta_value = %artist%

i.e. using = instead of LIKE.

You're also missing the quotes: %\"artist\"%, to exclude e.g. bartist or fartist ;-)

But you don't need to construct this SQL query by hand.

Better Alternatives

You could use the WP_User_Query class:

$users = new WP_User_Query( [ 'role' => 'artist' ] );

or it's get_users() wrapper instead:

$users = get_users( [ 'role' => 'artist' ] );

It will generate the SQL query for you, with the INNER JOIN.

3
  • fartist, well, that is hillarious, hahahaha. Just imagine.... Commented Jul 20, 2015 at 9:48
  • 1
    ps: I had this amazingly talented guy in mind that's mentioned in Wikipedia ;-) @PieterGoosen
    – birgire
    Commented Jul 20, 2015 at 10:01
  • 1
    If I wet my pants today, it will be all on you. This made the Monday more bareably ;-) Commented Jul 20, 2015 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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