Change WordPress User Roles in myPHP

Table of Contents

Estimated reading time: 3 minutes

If you are using WordPress, you may want to change user roles in bulk. But why?

This is down to my own personal experience where I had installed bbpress forum software. It was to start looking at providing support in a forum style.

However, I had not set up the user settings correctly and ended up with 1000’s of my visitors being set to bbpress user role of spectator rather than the WordPress user roles of Subscriber.

This then became a particular problem when I decided to remove bbpress, I had orphaned users all over the place. I needed a solution to change the user role of 1000’s of user accounts without having to go through each one manually.

myPHP Admin

This is where myPHP came in. You can use myPHP to amended data in your WordPress database. In this case, changing all users with spectator as their user role to subscriber.

NOTICE – Before you go any further, you are about to change your core WordPress database, so ensure that you have a good backup before going any further. You have been warned!

To carry this out, you need to login to your hosting cPanel and open up the myPHP Admin app:

When you click myPHP Admin it will automatically redirect you to the utility. Using the top menu bar, you should click on the SQL button:

This will now take you to the area where you can add your script to make the changes required.

myPHP WordPress User Role Change Script

Below is the script that you can use to update your own WordPress database by changing the users table and taking any user with bbp_spectator and changing it to subscriber.

UPDATE wp55_users
INNER JOIN wp55_usermeta
ON wp55_users.ID = wp55_usermeta.user_id
SET wp55_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
(
(wp55_usermeta.meta_key)='wp55_capabilities'
)
AND
(
(wp55_usermeta.meta_value)="a:1:{s:13:"bbp_spectator";b:1;}"
);

Note that the following will need to be changed to suit your environment:

wp55 = this is the WordPress database name
SET wp55_usermeta.meta_value = ‘a:1:{s:10:”subscriber“;b:1;}’ = Change the subscriber part to whatever you want the user role to be after the change is made
“a:1:{s:13:”bbp_spectator“;b:1;}” = This is the existing user role that you want to change

Once you are happy that everything is correct, you can click the Go button at the bottom right of the SQL page. This will then go off and make the changes you have asked to make.