We help IT Professionals succeed at work.

Users.php

lvollmer
lvollmer asked
on
I want to go into my wordpress MySQL and generate a report of all the users in my database. I basically want to mimic what is done in users.php in wp-admin.

Does anyone know the query to do this? I cannot figure it out. I just want to username, name, email and  role, and domain of blog they are assigned to (if possible)
Comment
Watch Question

Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Try this query. It will return a list of your user's Username, Name, Email and a field called meta_value.

The meta_value field will need to be parsed to get the role, as it contains other data, for example:

a:1:{s:13:"administrator";b:1;}

Not sure what the other info in there is, or whether it's that same format for every user.

I've also included a query that doesn't bother getting the role info.

Haven't got access to Wordpress MU, so I'm not sure about the domain info you need.








//If you need the role, try this
SELECT wp_users.user_login, wp_users.user_nicename, wp_users.user_email, wp_usermeta.meta_value
FROM wp_users, wp_usermeta
WHERE (wp_users.ID = wp_usermeta.user_id) AND wp_usermeta.meta_key = "wp_capabilities";

//If you don't need the role, then this will work
SELECT user_login, user_nicename, user_email
FROM wp_users

Open in new window

Author

Commented:
I def need the role. The second query works, however the first query provides 0 results.
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Give this a go. I've removed the WHERE clause and changed it to a LEFT JOIN. You'll probably get several records per user with this one, but if that works then we can filter the data to only show the role

SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value
FROM wp_users LEFT JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id;

Open in new window

Author

Commented:
ok it works, can we filter the data to show the role?
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Right,

My query above missed a bit you're gonna need - the value in the  meta_key field. Add this to the end of the select part of your query:
 
      , wp_usermeta.meta_key
 
In the records that are returned you should see some values in the meta_value field that look like

a:1:{s:13:"administrator";b:1;}

And next to it, a value in the meta_key field - that's the value you need to filter on.

Assuming the value in the meta_key field is wp_capabilities, you final query should look something like below.

The data stored in the meta_value field is a serialised array, and you can use the PHP unserialize function to get that data into an array.








SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value, wp_usermeta.meta_key
FROM wp_users LEFT JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
WHERE wp_usermeta.meta_key = "wp_capabilities";

Open in new window

Author

Commented:
SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value, wp_usermeta.meta_key
FROM wp_users LEFT JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
WHERE wp_usermeta.meta_key = "wp_capabilities";


that query provides 0 results
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
I'm assuming it returns results when you run it without the WHERE clause?

I'm also assuming that in those results you have fields called meta_key and meta_value

Look down the meta_value column and find a value that looks something like a:1:{s:13:"administrator";b:1;}

Does the value in the meta_key column say wp_capabilities





Author

Commented:
Correct, if I take the "where" clause out i get data like wp_3_capabilities, wp_11_capabilities, wp_4_capabilities etc etc
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ahh, OK.

Because you're using Wordpress MU, the meta_key value includes the Blog number. Change your WHERE clause to only check the capabilities part,

WHERE RIGHT(wp_usermeta.meta_key,12) = "capabilities";


Author

Commented:
perfect - thanks!