SQL query to delete secondary usergroup

[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