Bulk Edit via Sql?

  • Hi,

    So we have a large amount of users on the site that we would like to change their user role to a role we created with the help of the plugin.

    I know that it can be done via admin, however due to the large number of users & our firewall, its not possible to change all roles simultaneously & they are too many to do in chunks (would take ages). So we are looking for a more efficient way to do so.

    So I was wondering, is it possible for you to help me with sql query to be able to do so from phpmyadmin?

Viewing 1 replies (of 1 total)
  • Plugin Author Vladimir Garagulya

    (@shinephp)

    Hi,

    WordPress stores role granted to a user at the wp_usermeta DB table. You may select all subscribers this way:

    
SELECT * FROM wp_usermeta WHERE meta_key='wp_capabilities' AND meta_value='a:1:{s:10:"subscriber";b:1;}';

    ‘wp_’ is a DB prefix defined at the wp-config.php.
    ‘a:1:{s:10:”subscriber”;b:1;}’ is a serialized PHP array with 1 element key=’subscriber’ and value=boolean true. Pay attention on the length of string ‘subscriber’ – s: 10.
    So if you need to replace roles for all subscribers to contributor you can use this command:

    
UPDATE wp_usermeta SET meta_value='a:1:{s:11:"contributor";b:1;}' WHERE meta_key='wp_capabilities' AND meta_value='a:1:{s:10:"subscriber";b:1;}';

    Make a fresh backup copy of your database before to make any direct update. Just in case something will go a wrong way.

Viewing 1 replies (of 1 total)
  • The topic ‘Bulk Edit via Sql?’ is closed to new replies.