19

How do I get a list of all users with role = 'Customers' including all the metadata per user, means wp_users + wp_usermeta.

Query blow does not generate the wanted results.

$query = "SELECT * FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id ORDER BY ID DESC');  ";
            
$data = $wpdb->get_results($query,ARRAY_A);

But that list is not correct. It shows each user x times depending on the number of rows in wp_usermeta.

How can I filter it? That each user shows per record all his user + metadata?

3
  • Check this
    – bravokeyl
    Jun 29, 2016 at 13:46
  • but then I need to different calls 1 for user 1 for metadata. How do I combinate them?
    – Hermants
    Jun 29, 2016 at 13:51
  • 1
    And that call is??
    – Hermants
    Jun 29, 2016 at 13:58

4 Answers 4

29

I think you should use wp-api functions which will do everything for you.

  • get_users() function will get all users data just define fields which u require.
  • get_user_meta() function will get usermeta data.
 
$users = get_users( array( 'fields' => array( 'ID' ) ) );
foreach($users as $user){
        print_r(get_user_meta ( $user->ID));
    }
2
  • 3
    But will this not make one query per user to the database? Is there any way to do it with fewer queries? Aug 17, 2019 at 8:42
  • 6
    When WP fetches an object such as a user/post/term it also pre-fetches its meta and puts it in wp_cache to reduce the number of queries and speed up things. Those get_user_meta calls won't be making database queries, the data was already fetched when get_users was called.
    – Tom J Nowell
    Jun 26, 2020 at 12:57
2

A bit more formal way to achieve the same results are as follows Substitute any role as necessary and this code assumes you are using WooCommerce for more detailed information.

function GetSubscriberUserData()
{
  $DBRecord = array();
  $args = array(
      'role'    => 'Subscriber',
      'orderby' => 'last_name',
      'order'   => 'ASC'
  );
  $users = get_users( $args );
  $i=0;
  foreach ( $users as $user )
  {
    $DBRecord[$i]['role']           = "Subscriber";
    $DBRecord[$i]['WPId']           = $user->ID;
    $DBRecord[$i]['FirstName']      = $user->first_name;
    $DBRecord[$i]['LastName']       = $user->last_name;
    $DBRecord[$i]['RegisteredDate'] = $user->user_registered;
    $DBRecord[$i]['Email']          = $user->user_email;

    $UserData                       = get_user_meta( $user->ID );
    $DBRecord[$i]['Company']        = $UserData['billing_company'][0];
    $DBRecord[$i]['Address']        = $UserData['billing_address_1'][0];
    $DBRecord[$i]['City']           = $UserData['billing_city'][0];
    $DBRecord[$i]['State']          = $UserData['billing_state'][0];
    $DBRecord[$i]['PostCode']       = $UserData['billing_postcode'][0];
    $DBRecord[$i]['Country']        = $UserData['billing_country'][0];
    $DBRecord[$i]['Phone']          = $UserData['billing_phone'][0];
    $i++;
  }
  return $DBRecord;
}
0

I created a simple, free plugin, called Flat Meta Data, that flattens post and user meta data into a CSV that can more easily be used for reporting and analysis. In the process it creates tables in your database ([:prefix]postmeta_flat and [:prefix]usermeta_flat) that contain the same information and can be used in more complex queries. These tables have one row per post or user and one column per data point (meta_key) containing the meta values (meta_value).

After installing and activating the plugin (in wp-content/plugins/flat-table), it is accessible through a link in the main menu of the admin panel, called Flat Meta Data. You can then click on either the "Download post meta data" or "Download user meta data" button to generate the table and download the CSV containing all of the data in spreadsheet form.

A few notes:

  1. This plugin is provided free of charge. Use at your own risk.
  2. CREATE TABLE privileges are required. This shouldn't be an issue, but could be if you've restricted access to the WordPress database user.
  3. Generating and downloading a complete set of meta data can negatively impact system performance. Please do so only during off-hours or, better yet, on a sandbox populated with your live data.

Please let me know if you have any questions. Thanks.

3
  • 1
    Hello and welcome! Can you update your answer to explain how it does this? Answers need to be self contained, they can't rely entirely on offsite links.
    – Tom J Nowell
    Jun 25, 2020 at 10:19
  • 1
    Thanks for the guidance, Tom. Hopefully my edits suffice. Please let me know if you need any clarification.
    – Tim Burch
    Jun 26, 2020 at 12:49
  • Ah but that's just how to use the plugin though :( You need to answer the question, and a plugin doesn't do that, you can link to offsite places as further reading or as examples but the answer needs to contain the answer in its text. Linking to a plugin is just a recommendation, not an answer. Reading this I stll have no idea how your plugin solves ther problem, there's no technical detail, which is a shame because it's a great example to demonstrate technical skill and answer the question practically
    – Tom J Nowell
    Jun 26, 2020 at 12:55
0
$members = get_users(
    array('role'    => 'company',
        'orderby' => 'ID',
        'order'   => 'ASC'
    )
);
array_map( function($member ){
  $member->usermeta =  array_map(function($data){
    return reset($data);
  }, get_user_meta( $member->ID ) );
  return $member;
}, $members); 

This will return data as follows

Array
(
    [0] => WP_User Object
        (
            [data] => stdClass Object
                (
                    [ID] => 20
                    [user_login] => aastha
                    [user_pass] => $P$BVmRgRiZyZWhMvGszAkquPKX0JjedF1
                    [user_nicename] => aastha
                    [user_email] => [email protected]
                    [user_url] => 
                    [user_registered] => 2021-11-29 10:43:21
                    [user_activation_key] => 
                    [user_status] => 0
                    [display_name] => aastha
                    [usermeta] => Array
                        (
                            [nickname] => aastha
                            [first_name] => 
                            [last_name] => 
                            [description] => 
                            [rich_editing] => true
                            [syntax_highlighting] => true
                            [comment_shortcuts] => false
                            [admin_color] => fresh
                            [use_ssl] => 0
                            [show_admin_bar_front] => true
                            [locale] => 
                            [wm_capabilities] => a:1:{s:7:"company";b:1;}
                            [wm_user_level] => 0
                            [name] => btc
                            [type] => 7
                            [status] => active
                            [company_age] => 10
                            [company_member] => 30
                            [address] => indoor
                            [city] => indoor
                            [pin] => 5454
                            [telephone] => 5434
                            [opening_time] => 09:00
                            [closing_time] => 20:00
                            [fax] => gfgf
                            [skull_from] => wednesday
                            [skull_to] => sunday
                            [mobile] => 3987665213
                            [overview] => ghgh
                            [username] => aastha
                            [email] => [email protected]
                            [wesite] => http://www.aastha.com
                            [skull_logo] => 
                        )

                )

            [ID] => 20
            [caps] => Array
                (
                    [company] => 1
                )

            [cap_key] => wm_capabilities
            [roles] => Array
                (
                    [0] => company
                )

            [allcaps] => Array
                (
                    [read] => 1
                    [level_0] => 1
                    [company] => 1
                )

            [filter] => 
            [site_id:WP_User:private] => 1
        )
);

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.