SQL query to delete secondary usergroup

[fotografik]

Due to some recent changes in usergroups, some members inadvertently ended up with their PRIMARY and SECONDARY usergroups being duplicated.

This is causing issues when these members request for a new password and the systems throws them into a loop where they are unable to get their primary usergroup changed from Email Verification back to their normal usergroup as the system already has them tagged to the secondary usergroup.

Anyway, i am looking for a SQL query to manually delete the secondary usergroup of the affected members (about 10K of our 130K members) which will be too much to do manually.

Thanks!

[fotografik]

Anyone? Help? Please??

[kh99]

I don't know how to do it in SQL, but you could use this script:

PHP Code:

<?php
    
    require_once("global.php");
    
    // remove this group from users who have this group as a membergroup (secondary group)
    $usergroupid = X; // change to group id to remove
    $casesqlm = $casesqli = '';
    $updateusersm = $updateusersi = array();

    echo ("Removing users from secondary group id=" . $usergroupid . "<BR/><BR/>\r\n");
    flush();
    $users = $db->query_read("
        SELECT userid, username, membergroupids, infractiongroupids
        FROM " . TABLE_PREFIX . "user
        WHERE FIND_IN_SET('" . $usergroupid . "', membergroupids)
    ");
    
    if ($db->num_rows($users))
    {
        while($user = $db->fetch_array($users))
        {
            if (!empty($user['membergroupids']))
            {
                $membergroups = fetch_membergroupids_array($user, false);
                foreach($membergroups AS $key => $val)
                {
                    if ($val == $usergroupid)
                    {
                        unset($membergroups["$key"]);
                    }
                }
                $user['membergroupids'] = implode(',', $membergroups);
                $casesqlm .= "WHEN $user[userid] THEN '$user[membergroupids]' ";
                $updateusersm[] = $user['userid'];
            }
        }

        // do big update to get rid of this usergroup from matched members' membergroupids
        if (!empty($casesqlm))
        {
            $db->query_write("
                UPDATE " . TABLE_PREFIX . "user SET
                membergroupids = CASE userid
                $casesqlm
                ELSE '' END
                WHERE userid IN(" . implode(',', $updateusersm) . ")
            ");
        }
}

die("Done.");

First you probably want to make a backup of your database, or at least the user table. And you might want to close your forum while you run this.

Next, create a new php file (like maybe removegroup.php) and put in the above code. Then change this line

PHP Code:

    $usergroupid = X; // change to group id to remove 


to change X to the group id your want to remove. Then run the script (go to it with your browser).

When you're done you'll probably want to remove it.

BTW, this code comes from admincp/usergroup.php where it's used to remove users from a group when the group is being deleted.

[nerbert]

I was just about to start a script to do this but kh99 beat me to it.

One thing I would suggest is place that entire script inside a conditional so it runs for the admin only and then it can be a plugin in showthread start

PHP Code:

if($vbulletin->userinfo['userid'] == 1)
{

   SCRIPT

} 


I do this whenever I have to do any DB changes. It saves having to create a new file with all the standard requires.

[kh99]

Originally Posted by nerbert

I was just about to start a script to do this but kh99 beat me to it.

One thing I would suggest is place that entire script inside a conditional so it runs for the admin only and then it can be a plugin in showthread start ...

lol...it seems to happen a lot, either no one's doing it or two of us are. Anyway, I used to use hook misc_start for these things and pick a value for "do", but then someone pointed out that you really only need to include global.php, so that seemed easy enough. But that's a good point, if someone prefers to do it via plugin, that has it's advantages.

[fotografik]

Thanks for the replies - not exactly what I was looking for and will have to discuss with the other SysAdmin who is a little more well-versed than I am with MySQL.

[TheLastSuperman]

I have not tested this but you can try:

UPDATE user SET membergroupids = REPLACE(membergroupids,'2','');

So based on that, it reads:

Update all users, set their member group id (secondary usergroup) to "Blank" or '' where it currently is 2 i.e. Registered User/Member.

Now go through and do this for all the secondary usergroups listed in the usergroup manager so if you have a custom usergroup none of us have lets call it Main VIP which is usergroupid #29 you simply use this now:

UPDATE user SET membergroupids = REPLACE(membergroupids,'29','');

^ This should clear out all the secondary usergroups yet leave the primary remaining. If your asking how to do this query be sure to run a backup beforehand just to be safe.

Edit: Ahh I had to install a staging site on my localhost tonight regardless so tested and it works .

[nerbert]

I'm not clear on something: Is "Email Verification" a custom usergroup or do you mean "Users Awaiting Email Confirmation" (group 3)? Are several usergroups duplicated in secondary usergroups?

[kh99]

Originally Posted by TheLastSuperman

I have not tested this but you can try:...

Good job - I thought about ways to do it but couldn't come up with any. Since that text field can be a comma-separated list if the user belongs to more than one secondary group, I was thinking of handling a possible comma. But the code that reads and explodes that field is pretty forgiving [S]so I think your solution works.[/S]. I changed my mind...see the later posts.

Also, I believe he only wanted to remove on secondary user group, where it duplicates the primary group.

[nerbert]

Originally Posted by kh99

Good job - I thought about ways to do it but couldn't come up with any. Since that text field can be a comma-separated list if the user belongs to more than one secondary group, I was thinking of handling a possible comma. But the code that reads and explodes that field is pretty forgiving so I think your solution works.

Wouldn't it leave a double comma where the number was excised?