SQL User Query by Multiple Roles using PHP

admin管理员组

文章数量:1410731

I can successfully get all my WordPress users using the following code:

global $wpdb;
$sql = 'SELECT * FROM ' . $wpdb->users;
$users = $wpdb->get_results( $sql, 'ARRAY_A' );

However, I need to filter the users with multiple roles (only get "role1" and "role2"). I have tried various methods including the following which does not work:

global $wpdb;
$sql = '
    SELECT ID, display_name FROM wp_users 
    INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  
    INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) 
    WHERE 1=1 
    AND ( 
      ( 
        ( 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role1%' )
        ) 
        AND 
        ( 
          ( 
            ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role2%' )
          )
        )
      )
    ) 
    ORDER BY user_login ASC
    ';
$users = $wpdb->get_results( $sql, 'ARRAY_A' );

I can successfully get all my WordPress users using the following code:

global $wpdb;
$sql = 'SELECT * FROM ' . $wpdb->users;
$users = $wpdb->get_results( $sql, 'ARRAY_A' );

However, I need to filter the users with multiple roles (only get "role1" and "role2"). I have tried various methods including the following which does not work:

global $wpdb;
$sql = '
    SELECT ID, display_name FROM wp_users 
    INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  
    INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) 
    WHERE 1=1 
    AND ( 
      ( 
        ( 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role1%' )
        ) 
        AND 
        ( 
          ( 
            ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role2%' )
          )
        )
      )
    ) 
    ORDER BY user_login ASC
    ';
$users = $wpdb->get_results( $sql, 'ARRAY_A' );

• wp-query
• users
• mysql
• user-roles
• sql

Share Improve this question asked Nov 27, 2019 at 22:06 MichaelMichael 28111 silver badge1414 bronze badges 3

• 4 you can do that with the function get_users. – Kaperto Commented Nov 27, 2019 at 22:31
• Thank you. That worked... – Michael Commented Nov 27, 2019 at 23:03
• @Kaperto can you post your answer as an answer? – Tom J Nowell ♦ Commented Nov 27, 2019 at 23:10

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Taking @Kaperto's advice, I did the following which works:

$user_query = new WP_User_Query( array(
    'role__in'    => ['role1','role2''],
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
) );   
$users = $user_query->get_results();

本文标签： SQL User Query by Multiple Roles using PHP