1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Bulk changing user group query

Discussion in 'XenForo Questions and Support' started by BareMettle, Dec 10, 2012.

  1. BareMettle

    BareMettle Member

    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
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    Something like this should work:

    Code:
    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 :p
     
    Jake Bunce likes this.
  3. BareMettle

    BareMettle Member

    Awesome, I'll give that a go, thanks! :)
     
  4. BareMettle

    BareMettle Member

    Worked a treat, thanks again Chris!
     
    Chris D likes this.
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Admin CP -> Tools -> Rebuild Caches -> Rebuild User Caches
     
    Chris D likes this.
  6. Michael Murguia

    Michael Murguia Active Member

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

    Thanks!
     
  7. wang

    wang Well-Known Member

    Use the secondary_group_ids field instead of the user_group_id field in the query.
     
    Michael Murguia likes this.
  8. Michael Murguia

    Michael Murguia Active Member

    Awesome! Thank you!
     
    wang likes this.
  9. wang

    wang Well-Known Member

    You are welcome sir.
     
    Michael Murguia likes this.
  10. Michael Murguia

    Michael Murguia Active Member

    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:
    Code:
    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!
     
  11. wang

    wang Well-Known Member

    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.

    Code:
    UPDATE xf_user SET  secondary_group_ids =  '5, 3, 6, 7' WHERE email = 'email@gmail.com';
     
    Last edited: Dec 21, 2015
  12. Michael Murguia

    Michael Murguia Active Member

    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?
     
  13. Michael Murguia

    Michael Murguia Active Member

    Also, running this SQL Query:

    Code:
    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:

    Code:
     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
     
  14. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    @Michael Murguia

    Code:
    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');
    
     
  15. Michael Murguia

    Michael Murguia Active Member

    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
     

Share This Page