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)

fotografik

11-13-2011 10:54 PM

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!

fotografik

11-17-2011 09:03 PM

Anyone? Help? Please??

kh99	11-17-2011 10:15 PM

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

11-17-2011 10:27 PM

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	11-17-2011 10:34 PM

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.

fotografik

11-21-2011 04:11 AM

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

11-21-2011 04:42 AM

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

nerbert

11-21-2011 11:46 AM

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	11-21-2011 12:01 PM

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.

nerbert

11-21-2011 12:07 PM

Quote:

Originally Posted by kh99 (Post 2270329)

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

kh99	11-21-2011 12:12 PM

Quote:

Originally Posted by nerbert (Post 2270333)

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

Exactly - or a starting or ending comma. But last time I checked, the vb code that reads secondary groups explode()s the string then ignores any non-numeric, null, or duplicate values, so I'm pretty sure it wouldn't matter. Still, a mod might read that field and not be able to handle it, so I suppose there's some risk.

nerbert

11-21-2011 12:16 PM

I think you would need

REPLACE(REPLACE(membergroupids, '2,', ''), ',2', '' )

First it gets rid of 2 everywhere but at the end, then catches it at the end.

kh99	11-21-2011 12:18 PM

Yeah, I think that works. But you need a third REPLACE in case the field is only "2".

Pretty cool.

nerbert

11-21-2011 12:36 PM

Code:

REPLACE(

     REPLACE(

          REPLACE(

               REPLACE(membergroupids, '2', ' '), ', ,', ''), ' ,', ''), ', ', '')

EDIT: replace 2 with a one character blank space instead of nothing. Be sure to test this where it can't do any damage to the real table

kh99	11-21-2011 01:16 PM

You know, I just thought of something (and this applies to TheLastSuperman's solution as well) - you can't just replace all 2's because someone could have a usergroup 12, or 20 something. I think you'd need a regular expression to match ^2, and ,2$ and well as ,2,.

Edit: I guess what I should say is that I think it will work for a one-time removal of some secondary groups if there's no conflict as is mentioned above. For instance if you want to remove secondary group 9 and you have less than 19 usegroups, it'll work fine.

nerbert

11-21-2011 01:27 PM

If I were doing this I would select a batch and loop through it using php to manipulate the strings and then update one at a time in the loop. It would be slow but I'd rather have slow and predictable than fast and unpredictable. You could also check what's going on before running it with the update query and be sure you aren't messing up. If the whole process takes an hour to run, so be it.

--------------- Added [DATE]1321890191[/DATE] at [TIME]1321890191[/TIME] ---------------

First I would use a SELECT That uses LIKE to match the membergroupid.

I would use this php in a loop. If everything looks OK then replace the echo with an update

PHP Code:


		
			
$m = $membergroupids;
$m = ',' . $m . ',';
$m = str_replace(',2,', ',', $m);
$m = substr($m, 1, -1);
echo $membergroupids . '  --------------->  ' . $m . '<br>';

--------------- Added [DATE]1321914118[/DATE] at [TIME]1321914118[/TIME] ---------------

Here's the first thing I would do. Use this for a plugin in showthread_start. All it does is check that the string manipulation is working exactly as it should. You should have a white page with 10 lines each showing user name, primary usergroup, original secondary usergoup string and finally the modified secondary user group string. Edit in your userid in the first line where it says '1'

PHP Code:


		
			


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

{

    ini_set('display_errors', '1');

    $users = $db->query_read(" 

        SELECT usergroupid, username, membergroupids 

        FROM " . TABLE_PREFIX . "user 

        WHERE usergroupid <> '6'

        AND CONCAT(',', membergroupids, ',') LIKE CONCAT(',', usergroupid, ',')

        LIMIT 10 

        "); 

    

    if ($db->num_rows($users) > 0) 

    { 

        while($user = $db->fetch_array($users)) 

        {

            $n = $user['username'];

            $u = $user['usergroupid'];

            $m1 = $user['membergroupids'];

            $m2 = ',' . $m1 . ',';

            $find = ',' . $u . ',';

            $m2 = str_replace($find, ',', $m2);

            $m2 = substr($m2, 1, -1);

            echo $n . ' ; ' .$u . ' ; ' . $m1 . ' -----> '. $m2 . '<br><br>';

        }

        die($db->num_rows($user) . " found");

    }

    else

    {

        die("no users found");

    }

}

If it's working perfectly I'll write a new one with an UPDATE query

All times are GMT. The time now is 07:04 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.03747 seconds Memory Usage 1,818KB 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 (3)bbcode_code_printable (5)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (16)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 bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: