[SQL] Sorting users into one group

[eMike]

On my forum, a gamingforum, I have loads of members who registers username syntaxes like "Clan_Nickname" or "Guild_Nickname".

I am in need of sorting this out, for example (and more specifically) getting all users with the "Clan-A_" prefix into usergroup "Clan A" or with the "Guild-B_" prefix nto usergroup "Guild B"

How would I go about doing that? (Except doing it manually)

I do not know much about databases, but im hoping that with the use of some magic SQL command in the SQL Query section of the vB ACP, that this can be done.

Any help would be sooo very appreciated!

[Marco van Herwaarden]

Select them would not be a real problem. Changing usergroups might be, although i can think of some tricks.

Are you talking about primairy or seconday usergroups?
Is this a 1 time action or ongoing?

[eMike]

All are primary I believe, and it would be a 1-time action only.

[Marco van Herwaarden]

The most secure way if you are not using reputation that is, would be the following:

[sql]UPDATE user set reputation = 99999999 WHERE username LIKE 'Clan-A_%'[/sql]
Then create a Usergroup Promotion that will promote the user to the correct usergroup if they have > 99999998 reputation. Wait until the promotion has run.

Then reset (rebuild or reset with SQL) reputation, and go repeat the process for the next group.

Make sure that if you have 'Clan_Member' and 'Clan_A_Member' you process the longest identifier first, and exclude the longer group when you go processing 'Clan_%'.

I have not tested this, so first try on a testboard. Also make sure you have good backups.

[eMike]

Originally Posted by MarcoH64

The most secure way if you are not using reputation that is, would be the following:

[sql]UPDATE user set reputation = 99999999 WHERE username LIKE 'Clan-A_%'[/sql]
Then create a Usergroup Promotion that will promote the user to the correct usergroup if they have > 99999998 reputation. Wait until the promotion has run.

Then reset (rebuild or reset with SQL) reputation, and go repeat the process for the next group.

Make sure that if you have 'Clan_Member' and 'Clan_A_Member' you process the longest identifier first, and exclude the longer group when you go processing 'Clan_%'.

I have not tested this, so first try on a testboard. Also make sure you have good backups.

It worked like a dream Marco!
My deepest and sincerest thanks! That saved me for endless upon endless hours of manual nightmares.

[Marco van Herwaarden]

Good tho hear that my not very standard solution worked.