How can I change many WordPress user levels using SQL in phpmyadmin?

Hello good people,

I have a WordPress site with many users.

The site uses s2Member for access control.

s2Member adds User Levels in addition to the WordPress User Roles.

I need to change several user's User Level/Role to be "subscriber".

I think it will be something like:

SELECT 'jack34, jane01, judy66, johnboy, etc, etc, etc'
FROM wp_usermeta
WHERE wp_user_level='s2member_level2'
OR wp_user_level='s2member_level2'
OR wp_user_level='s2member_level3';
UPDATE wp_usermeta
SET wp_user_level='subscriber';

...or should it be something like:

SELECT 'jack34, jane01, judy66, johnboy, etc, etc, etc'
FROM wp_usermeta
UPDATE wp_usermeta
SET wp_user_level='subscriber';

I don't know if I should use the s2Member "Role" or the WordPress "wp_user_level".

And, I'm not certain if it should be done in the "wp_usermeta" table or in the "wp_users" table.

Can you good SQL wizards make sure I have the correct code to run a clean query for this?

Thank you,

Chris
dotsonpaperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
I wouldn't even go there... there are plenty of plugins out there that can do that for you and changing the wordpress database directly may leave it unusable

see here: http://wordpress.org/plugins/user-role-editor/
dotsonpaperAuthor Commented:
Hello tagit,

Thank you for the response.

I've looked at several plugins but all of them cannot do what SQL can do in one pass.

I need to update hundreds of user records from a user table with thousands of records.

If I use any plugin I'll need to use some variant of the WordPress Users interface which will require a very long time manually going through all the members, or even searching for them one by one. Imagine doing that manually.

I use SQL a few times each month to do things in batches--that's why SQL is used--it let's us reliably perform actions on specific databases, specific tables, to specific users with specific changes.

I just need the correct SQL statement to run.

I'll be doing this with SQL as soon as I get a clean example from an SQL person (who can see from the tags on this post that this is Database and SQL related).

Chris
Jason C. LevineDon't talk to me.Commented:
You have the right idea but the usermeta table stores the settings as key->value pairs so the SQL should look something like this:

UPDATE wp_users 
INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
SET wp_usermeta.meta_value = "subscriber"
WHERE (((wp_usermeta.meta_key)="s2_member_level") 
AND ((wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"));

Open in new window


I don't use S2 Member so I don't know what their keys are named.  A quick look at wp_usermeta should get the right information for the meta_ley
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Jason C. LevineDon't talk to me.Commented:
To do it with just the WordPress user role it would be:

UPDATE wp_users 
INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE (((wp_usermeta.meta_key)='wp_capabilities'));
AND ((wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"));

Open in new window


tagit is right that doing this directly in the SQL can kill your site.  Backup, backup, backup.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dotsonpaperAuthor Commented:
Jason,

Thank you for the SQL. I'm trying the second query code because it matches the fields in the db.

When I test ran your exact code, with real user names or with the given user names, I get this error in phpmyadmin:

Error

SQL query:

AND (
(
wp_users.user_login
) = "john" OR (
wp_users.user_login
) = "jane" OR (
wp_users.user_login
) = "jack" OR (
wp_users.user_login
) = "jim"
));

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND ((wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.u' at line 1

So I went online to search for mySQL validators and most of them don't work, but the few which did work indicated similar errors. Of course they can't account for the exact database and elements in WordPress, or in this specific installation.

I'm going to test tuning the query you shared to see if I can get it to work.

Chris
RobOwner (Aidellio)Commented:
There's a semicolon that needs to be removed after the WHERE statement and just before the AND

should read:

UPDATE wp_users 
INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE (((wp_usermeta.meta_key)='wp_capabilities'))
AND ((wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"));

Open in new window

dotsonpaperAuthor Commented:
Thank you tagit,

I'll try the SQL you updated.

The closest SQL I was able to get was:

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
(
((wp_usermeta.meta_key)='wp_capabilities')
AND
((wp_users.user_login)="john")
OR
((wp_users.user_login)="jane")
OR
((wp_users.user_login)="jack")
OR
((wp_users.user_login)="jim")
);

AFTER backing up the database, when I ran that in phpmyadmin it said 52 rows affected. However all of the users in the database were demoted to subscriber.
Jason C. LevineDon't talk to me.Commented:
My code illustrates how to change the WordPress user role.  Since I don't use S2 Member, I don't know exactly what it does to set its own role or if it piggybacks on the WordPress stuff.  You should examine the wp_usermeta table to see if there are keys specific to S2 Member and if so, which one sets the role.  

From there, you can modify my code to update things properly.
dotsonpaperAuthor Commented:
Hello,

When I run:

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE (((wp_usermeta.meta_key)='wp_capabilities'))
AND ((wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"));

I get: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6"
dotsonpaperAuthor Commented:
Hey Good news!

I'm double checking the results, but when I run:

UPDATE wp_users
INNER JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
SET wp_usermeta.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
WHERE
(
(wp_usermeta.meta_key)='wp_capabilities'
)
AND
(
(wp_users.user_login)="john" Or (wp_users.user_login)="jane" Or (wp_users.user_login)="jack" Or (wp_users.user_login)="jim"
);

I get:

4 rows affected. (Query took 0.1750 sec)

I verified user stats before and after the SQL run and the correct number changed. And I verified the four usernames in the sample and they were all demoted to subscriber.

And for anyone reading this--DO backup your database before trying this.
dotsonpaperAuthor Commented:
IMPORTANT NOTE FOR READERS

 jason1178 and tagit gave the solution so they get the points --

...however, this site makes me award points to answers which in this case are not exactly correct.

The code which did work is in my "Good News" post just above this post.

Thank you Jason1178 and tagit!

Chris
RobOwner (Aidellio)Commented:
Yes BACKUP or use a plugin if it suits as these invariably will do a backup for you or at the very least an undo of sorts.

It certainly sounds like you're there.  I do want to stress that when you upgrade your Wordpress at anytime in the future you may find this doesn't work anymore because the table structure has changed.  If this is a once off so be it but I prefer to rely on a plugin such as http://vip.wordpress.com/2012/07/12/bulk-user-management/
dotsonpaperAuthor Commented:
Experts-Exchange and Jason1178 and tagit saved me hours of work.

Imagine manually editing over 1,300 user records in WordPress.

1. Enter user name and click search, wait...
2. When user name appears, click to edit, wait...
3. When edit screen appears, select new user level,
4. Click to save update

...and repeat.

On a normal internet connection with a normal WP install, that four step cycle would be easily two minutes per. Factor that out over 1,300 users and you get 43.33 hours of work--if you do not count the errors and mistakes which always happen with manual repetitive repetitive repetitive tasks.

Thank you again jason1178 and tagit.

Chris
Jason C. LevineDon't talk to me.Commented:
...however, this site makes me award points to answers which in this case are not exactly correct.

Technically you could have accepted your own post as the solution and then handed out assist points, but whatever :)

because the table structure has changed.

wp-usermeta is unlikely to undergo radical change in the near or distant future.

http://vip.wordpress.com/2012/07/12/bulk-user-management/

It's a good plugin but starts to get unwieldy with more than a few hundred users.

Factor that out over 1,300 users and you get 43.33 hours of work

Nice...how much do you charge per hour?

Thank you again jason1178 and tagit

You're welcome!
RobOwner (Aidellio)Commented:
Not a problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WordPress

From novice to tech pro — start learning today.