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

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

$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


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]

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]

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 so I think your solution works.. 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]

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?

[kh99]

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]

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]

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

Pretty cool.

[nerbert]

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]

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]

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 1321890191 at 1321890191 ---------------

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


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



--------------- Added 1321914118 at 1321914118 ---------------

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'



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