4

I need to get all the users who have joined (are members) of a site (blog) in WordPress multisite. To complicate it, I am doing this outside of WordPress and don't have access to internal Wordpress functions, so need to construct the SQL directly.

In English the SQL would break down as "get an array of user IDs of users that are members of site x" (where site relates to one of the WordPress Multisite sites).

I've tried going through WordPress code to understand, but struggling with the above.

I don't need the PHP, I can work that out, just an example SQL statement.

Many thanks!

Simon Goodchild
  • 125
  • 2
  • 9
  • are there any API's available that utilize SQL that you know of...? – MethodMan Dec 16 '14 at 00:37
  • 1
    Not that I'm aware of. I can probably make the question simpler, "how does WordPress store in its database which users are members of each subsite" if that helps? I can then work out the SQL. – Simon Goodchild Dec 16 '14 at 00:39

2 Answers2

4
  1. select * from wp_blogs

From the output of the command note down the blog_id you want the users of. For eg: say you are wanting the users of the site with blog_id = 2 , next run the following query.

  1. select * from wp_users u join wp_usermeta um on u.id=um.user_id where um.meta_key="wp_2_capabilities"
Anand Shah
  • 14,575
  • 16
  • 72
  • 110
  • It's not a very good idea to use custom SQL to do this. And it's a very bad idea to write it this way - ignoring table prefix, and other stuff like that. – Krzysiek Dróżdż Jul 10 '18 at 07:19
  • 2
    @KrzysiekDróżdż Why is that? It's not the prettiest, but using custom SQL is very necessary within WordPress sometimes. Using it for 10+ years now, I have to say there are some solutions that can only be done with SQL. Yes, should add $wpdb->prefix though before the tables. – Allen Gingrich Jan 08 '19 at 23:32
  • Not only before tables - also before capabilities field ;) And it’s not a good idea because of filters. You query for some users, but you ignore all filters - and some users may be disabled and so on... Even the name of that field may be changed... So yes - your code may work just fine, but it also may not. That’s why you should always use functions and API, if it’s available... – Krzysiek Dróżdż Jan 08 '19 at 23:34
  • 1
    @KrzysiekDróżdż `I am doing this outside of WordPress and don't have access to internal Wordpress functions, so need to construct the SQL directly.` [sic] - that's what the OP mentioned, so there's no WP involved. Agreed, with the prefix point, I should've mentioned it. I quote you - `should always use functions and API, if it’s available` in this case it clearly wasn't. At times using a plain SQL query may be the need of the hour and not always a bad idea. ;) – Anand Shah Jan 09 '19 at 13:51
  • Well, you can include WP and use WP functions in most cases. But still - if you really, really have to do it with SQL, be very careful - there are menu tiny details that may break your code or let your code break WP. – Krzysiek Dróżdż Jan 09 '19 at 15:05
0

Thanks for this code, just what I needed!

If it helps anyone else, I also extended it a bit to get how many people have a given role, e.g. a custom role teacher was used below:

select * from wp_users u join wp_usermeta um on u.id=um.user_id where um.meta_key="wp_2_capabilities" AND um.meta_value LIKE '%teacher%'

This of course requires other roles not to contain the word teacher (e.g. maybe there's a role ex-teacher which it would also pick up). That was the case for me so I ran two queries and subtracted the two numbers.

If anyone knows how to do it with one query that would be nice?

teo van kot
  • 12,350
  • 10
  • 38
  • 70
Trelawney
  • 3
  • 1