vb.org Archive - SQL query to delete secondary usergroup

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)

- - SQL query to delete secondary usergroup (https://vborg.vbsupport.ru/showthread.php?t=273958)

SQL query to delete secondary usergroup

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!

Anyone? Help? Please??

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.

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.

Quote:

Originally Posted by nerbert (Post 2269057)

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.

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.

I have not tested this but you can try:

Code:

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:

Code:

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 ;).

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?

Quote:

Originally Posted by TheLastSuperman (Post 2270225)

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.

Quote:

Originally Posted by kh99 (Post 2270329)

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

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (3)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01689 seconds Memory Usage 1,764KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (3)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: