Bulk changing user group query

[BareMettle]

Hi,

Was wondering if anyone could help...

I need to give access to a locked down forum for about 1000 users, I've created a group and set the permissions, changing their primary group to this one works as I'd like it to (for some reason adding it as secondary doesn't seem to, but I'm not too worried about that as we don't plan on adding any more forums/groups anyway). I just don't want to have to do it by hand!

So I'd like to make a MySQL query that compares existing users' email addresses to a list I have and if they match change their primary group, but I'm struggling. Does anyone know how I might be able to do this? Or is there any mod that would help?

Thanks in advance,
Brendan

 

[Chris D]

Something like this should work:

UPDATE xf_user
SET user_group_id = '999'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com')

Don't forget to change the user_group_id

 

[BareMettle]

Awesome, I'll give that a go, thanks!

 

[BareMettle]

Worked a treat, thanks again Chris!

 

[Jake Bunce]

You need to run this afterwards to rebuild the permissions ids:

Admin CP -> Tools -> Rebuild Caches -> Rebuild User Caches

 

[Michael Murguia]

Something like this should work:

UPDATE xf_user
SET user_group_id = '999'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com')

Don't forget to change the user_group_id

Hi Chris! Is there a way to add a secondary user group using this method without changing the primary group?

Thanks!

 

[wang]

Hi Chris! Is there a way to add a secondary user group using this method without changing the primary group?

Thanks!

Use the secondary_group_ids field instead of the user_group_id field in the query.

 

[Michael Murguia]

Awesome! Thank you!

 

[wang]

Awesome! Thank you!

You are welcome sir.

 

[Michael Murguia]

One more question! WHen I update, I need to either:
1. add a group without removing groups from a user, or...
2. add multiple groups at once

I tried:

UPDATE xf_user
SET user_group_id = ('5','3','6','7')
WHERE email IN ('xxxxx@gmail.com')

With the error, "#1241 - Operand should contain 1 column(s)"

What am I doing wrong?

Thank you!

 

[wang]

One more question! WHen I update, I need to either:
1. add a group without removing groups from a user, or...
2. add multiple groups at once

I tried:

UPDATE xf_user
SET user_group_id = ('5','3','6','7')
WHERE email IN ('xxxxx@gmail.com')

With the error, "#1241 - Operand should contain 1 column(s)"

What am I doing wrong?

Thank you!

You should use the IN operand, which allows you to specify multiple values in a WHERE clause. The operand = is to add a single value. You must remove the brackets from the group ids.

Something like this should work.

UPDATE xf_user SET  secondary_group_ids =  '5, 3, 6, 7' WHERE email = 'email@gmail.com';

 

[Michael Murguia]

You should use the IN operand, which allows you to specify multiple values in a WHERE clause. The operand = is to add a single value. You must remove the brackets from the group ids.

Something like this should work.

UPDATE xf_user SET  secondary_group_ids =  '5, 3, 6, 7' WHERE email = 'email@gmail.com';

Hi there! Returning to this SQL query because I wanted to use it again. Question, will this remove other secondary groups from the user? If I set '6' for example, and they are already '5' will '5' be removed? If so, is there any way to prevent this?

 

[Michael Murguia]

Also, running this SQL Query:

UPDATE xf_user SET  secondary_group_ids =  '6' WHERE email = 'email1@gmail.com' 'email2@gmail.com' 'email3@gmail.com' 'email4@gmail.com' 'email5@gmail.com';

I get:

 0 rows affected. (Query took 0.0065 sec)

And I know for a number of the email addresses '6' was not set. What am I doing wrong.

THANKS in advance for your support!

M

 

[Jake Bunce]

@Michael Murguia

UPDATE xf_user
SET  secondary_group_ids =  '6'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com', 'email3@gmail.com', 'email4@gmail.com', 'email5@gmail.com');

 

[Michael Murguia]

@Michael Murguia

UPDATE xf_user
SET  secondary_group_ids =  '6'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com', 'email3@gmail.com', 'email4@gmail.com', 'email5@gmail.com');

Thank you Jake, I have another thread here if you are interested in helping further. It is more complete. I'm also interested in creating a plugin to do this. If interested in that, PM me (paid).

thanks!

M