XF 1.4 Query to Update Users

[Reeve of Shinra]

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)


thanks!!

 

[Steve F]

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.

 

[jeffwidman]

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
SET
    secondary_group_ids = 24
WHERE
    email IN (SELECT
            email
        FROM
           email_check_table
        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')"

 

[Reeve of Shinra]

Batch Update Users wont work for this.

 

[Reeve of Shinra]

Thanks Jeff

 

[Paul B]

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.

 

[jeffwidman]

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?

 

[Jake Bunce]

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

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.

 

[Reeve of Shinra]

so the query is okay then?

 

[borbole]

so the query is okay then?

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

 

[Jake Bunce]

so the query is okay then?

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.