Question 1906 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Assign role to imported users via SQL – WordPress

I imported like almost 600 users from a db inside wp_users and I’d like to assign default role to all users imported via SQL… I know capability is usually stored in wp_usermeta with metakey = ‘wp_capabilities’ and actually I see only administrator role in meta_value field….. How I can assign default subscriber role to all those almost 600 users via SQL? Thanks in advance to all!!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you O. Jones, at the end I decided to implement a little PHP procedure where I call wp_insert_user() because I read user data from the other table I am importing from with success! Thanks again!


  2. 0

    To set one user, if you know the ID, to a subscriber role, run these two SQL queries.

    INSERT INTO wp_usermeta (user_id, meta_key, meta_value) 
       VALUES (:id, 'wp_capabilities', 'a:1:{s:10:"subscriber";b:1;}');
INSERT INTO wp_usermeta (user_id, meta_key, meta_value) 
       VALUES (:id, 'wp_user_level', '0');

    The first query inserts a capabilities array like this:

    array ('subscriber' => true)

    and the second sets the user level to 0. User level is mostly a legacy setting, but some plugins require it.

    Setting all your new users’ capabilites and levels is slightly more tricky, because you need a list of the ones to set. If your newly imported users don’t have any wp_capabilities setting yet, you can put the list of user IDs in a temporary table like this:

    CREATE TEMPORARY TABLE new_users
SELECT wp_users.ID
  FROM wp_users
  LEFT JOIN wp_usermeta  
          ON wp_users.ID = wp_usermeta.user_id
         AND wp_usermeta.meta_key = 'wp_capabilities'
 WHERE wp_usermeta.user_id IS NULL;

    Then you can use that temporary table for inserting all the meta you need.

    INSERT IGNORE INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT ID, 'wp_capabilities', 'a:1:{s:10:"subscriber";b:1;}' FROM new_users;
INSERT IGNORE INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT ID, 'wp_user_level', '0' FROM new_users;

    Careful If you’re using a WordPress multisite instance the procedure is more complex. And, this depends on your new users having no capability or level settings at all.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search