SQL query to delete secondary usergroup

[kh99]

Originally Posted by nerbert

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 [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