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

XF 1.4 Query to Update Users

Discussion in 'XenForo Questions and Support' started by Reeve of Shinra, Dec 29, 2014.

  1. Reeve of Shinra

    Reeve of Shinra Well-Known Member

    I need help with two queries please :)

    Query 1: I am looking to change the primary user group to 24 which I will then purge via the admincp. Its the last part I'm having problems with.

    UPDATE xf_user
    SET user_group_id = 24
    WHERE user ID =

    The Value in User_ID (column) AND Column_X = 2 (column) in Email_Check_2014 (table)

    Query 2: I am looking to mark these users so they don't get emails and need to reconfirm their email before posting. I have a second user group created but it looks like they added a field for this in 1.3?

    UPDATE xf_user
    SET user_state = "email_confirm_edit"
    WHERE user ID =

    The Value in User_ID (column) AND Column_X = 1 (column) in Email_Check_2014 (table)

  2. Steve F

    Steve F Well-Known Member

    I think you should be able to use the batch update user tool.

    What group are the users in now? Remember in XF all users should be in the "Registered" group by default.
    Tracy Perry likes this.
  3. jeffwidman

    jeffwidman Active Member

    For the users you'll delete, you want something like this--I changed your cross-table match from "user_id" to "email" as that's likely easier if you've just got a column of invalid emails:

    UPDATE xf_user
        secondary_group_ids = 24
        email IN (SELECT
            WHERE Column_X = 'invalid')
    Note that this will wipe out any other secondary ID's for these users, not a problem if you're just going to delete them though.

    Also, I have a hunch for the users you are keeping that you want to set their user status to "Bounced" rather than "Awaiting confirmation (from edit)"... ;-)

    From what I understand, bounced status shows them a notice that they need to update their email address, versus "Awaiting confirmation" assumes Xenforo has already sent them a confirmation email and it's just waiting for them to click the link... but Xenforo hasn't sent them an email yet, so they can't click that link.

    For the bounces, you'll again use that sub-select structure of "WHERE email in (SELECT email FROM temp_email_check_table WHERE Column_X = 'string')"
  4. Reeve of Shinra

    Reeve of Shinra Well-Known Member

    Batch Update Users wont work for this.
  5. Reeve of Shinra

    Reeve of Shinra Well-Known Member

    Thanks Jeff
  6. Brogan

    Brogan XenForo Moderator Staff Member

    Don't change user groups using a query - it won't update the permission sets correctly and you will end up with a corrupted database.
  7. jeffwidman

    jeffwidman Active Member

    Okay, good to know.

    I thought it was okay because of @Jake Bunce post here: https://xenforo.com/community/threads/sql-for-moving-primary-to-sec-usergroups.73572/#post-761720

    Then how do I change the status of several thousand users when I can't pull them out using ACP search options?

    I hit this just last night myself. Spammers like to take a gmail address and add a random number of periods and plus signs because Gmail treats them as the same gmail address, but Xenforo sees them as separate accounts. So the spammer can use one Gmail account to create multiple spam Xenforo accounts.

    To find these, I copied my user table to another table and ran a Regex replace on gmail addresses in the temp table to strip out the periods/+ signs in the email address. Then I ran an Update query on my actual users table that put all users into a temporary second group if the user shared a gmail address with two or more other xenforo accounts. I identified ~5K spam accounts this way.

    There is no way I could have accomplished that type of query in the Batch Update users.

    1) I then deleted all users in this secondary group, will my permissions still be screwed up if the users don't exist?

    2) For scenarios like this where I don't delete the users--for example, I've got several thousand more users who I suspect are spammers but I'm not sure, so I want to lock down their permissions more than normal--what should I do?

    3) Per the @Jake Bunce post above, why won't updating the user cache fix this issue with permissions?
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Changing group memberships in the xf_user table is OK as long as you rebuild the user cache afterwards. The rebuild will update the permission combination ids in the xf_user records.
    jeffwidman and AndyB like this.
  9. Reeve of Shinra

    Reeve of Shinra Well-Known Member

    so the query is okay then?
  10. borbole

    borbole Well-Known Member

    Yes, but make a thorough backup of the db before you run it. For just in case.
  11. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Yes the query is OK. You can update xf_user.user_group_id and xf_user.secondary_group_ids with a query and then rebuild the user info:

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

    The xf_user.secondary_group_ids column can be tricky to update with a query since it is a comma list of group ids. But in your case you are setting it to just one group id which doesn't involve commas so it's easy. Note that this will overwrite any other secondary group memberships for those users.
    jeffwidman likes this.

Share This Page