SQL query for spliting users in groups

[Laur]

Hello,

I have a community of about 7000 users and I want to split them in small groups based on their username (group AD for usernames starting with A, B, C, D; group EH for usernames starting with E, F, G, H; etc etc) but I want to do it via SQL so I don't have to manualy edit every username.

The new group has to be Additional Usergroup, so their Primary Usergroup remains "Registered users".

Can anyone recomment me an easy way to do it?

Thank you in advance!

PS: I have no SQL knowledge so I will much appreciate exact SQL commands.

[Lats]

Quick & dirty, this will do what you what however, it will wipe out any existing additional usergroups. If you don't have any, that won't be a problem.

UPDATE
    user
SET
    membergroupids = 
CASE
    WHEN substring(username,1,1) IN('a','b','c','d') THEN '8'
    WHEN substring(username,1,1) IN('e','f','g','h') THEN '9'
    WHEN substring(username,1,1) IN('i','j','k','l') THEN '10'
    WHEN substring(username,1,1) IN('m','n','o','p') THEN '11'
    WHEN substring(username,1,1) IN('q','r','s','t') THEN '12'
    WHEN substring(username,1,1) IN('u','v','w','x','y','z') THEN '13'
END

You will most likely need to adjust the membergroupids to align with your requirements. At the moment, if their usernames begin with either a,b,c or d they will have an additional usergroupid (membergroupids) of 8 and so on.

Best to run in phpmyadmin.

[reefland]

Best to make a copy of your DB and run this query on the copy to make sure it does what you want before running on your live user table.

[Laur]

Thank you very much for the quick reply!

At this moment I only have the default 7 usergroups (administrators, moderators etc) and an usergroup for banned users. It is set to be primary usergroup for its users.

In the case I run those commands, will it wipe the info regarding the banned users usergroup?

[Lats]

No, but as mentioned, you will need to adjust the values (8,9,10 etc) to your new usergroups ids.

[AntonLargiader]

I'm looking for a query where I can mass update a list of users to make them all members of a certain usergroup, based on external information. I have a list of people with their userids and I want to add them all to a specific usergroup without erasing the existing custom groups that they belong to.

So, the above would work but it would wipe out the existing membergroupids values, which I can't do. Any idea how I can easily insert/remove a number in that string?

[Lats]

It would be something like this...

UPDATE
    user
SET
    membergroupids = 
CASE
    WHEN substring(username,1,1) IN('a','b','c','d') THEN IF(membergroupids = '','8', CONCAT(membergroupids,',8'))
    WHEN substring(username,1,1) IN('e','f','g','h') THEN IF(membergroupids = '','9', CONCAT(membergroupids,',9'))
    WHEN substring(username,1,1) IN('i','j','k','l') THEN IF(membergroupids = '','10', CONCAT(membergroupids,',10'))
    WHEN substring(username,1,1) IN('m','n','o','p') THEN IF(membergroupids = '','11', CONCAT(membergroupids,',11'))
    WHEN substring(username,1,1) IN('q','r','s','t') THEN IF(membergroupids = '','12', CONCAT(membergroupids,',12'))
    WHEN substring(username,1,1) IN('u','v','w','x','y','z') THEN IF(membergroupids = '','13', CONCAT(membergroupids,',13'))
END

I recall this coming up before, so check this thread too...

http://www.vbulletin.com/forum/showthread.php?288654-Can-I-do-this-with-a-single-query