Usergroup Manager

[Colossal31]

Afternoon everyone,

Ok so I have run into a dilemma that I have been searching for a solution for. I need to be able to remove 12-18 users out of a Secondary usergroup. But I need to be able to leave people that have a specific profile field value of 1-3 to remain in that group for that many clears.

Example:
Usergroupids 25, 30, 45, and 50 needs all users removed except users with profile_field _12 having a value of 2 or greater staying.

In the above situation they do not get moved to a new usergroup it just removes them from this secondary usergroup. I have about 30 usergroupids that I have to do this to. If someone knows of a mod that will help with this or an sql query that will work for this please let me know. I am not sql savvy so I couldnt write one myself. The two times I tried i killed my site and had to restore using a backup. Also if someone does know a way to do it with SQL is there a way when the query is run to reduce the profile field 12 number by 1 so that once that number gets below 2 they will be removed from the usergroup as well.

Thanks for any and all assistance.
Colossal31

[Gio~Logist]

If it's only 12-18 why not do it manually?

[Colossal31]

There is 30+ Usergroups. That is only one set because there is 5 other sets of 30 Usergroups some with more and some with less users that are on different time frames. If i have the query with the basic knowledge of SQL that I have I am sure I can adjust it to work for the other usergroups I was just trying to keep the post short and to the point of what I need.

But I suppose I should have stated I need this for more than just the initial 30+ usergroups. I run a sports league with multiple sports. Each Sport has 30+ teams(which means UG for each team) depending on the sport and each team varies in size of 12 to 18 people(Users in those UGs). I need to be able to clear the Usergoups of their members because next season that usergroup may have 12 new people because of the way our leagues work. And the Profile_Field_12(not the actual field) is a retainer field. So a 12 year old can play in the 12-14yr old league for 3 years so the profile field 12 says he has 3 years he is eligible to be in that league.

I hope the additional info makes this easier to understand my need.

Thanks for your time,
Colossal31

[Gio~Logist]

I'm sorry, I would need to see it in front of me in order to make sense of it. I can definitely give you the query I THINK it should be, but I could be wrong.

Example:
UPDATE user
LEFT JOIN
userfield on (user.userid = userfield.userid)
SET user.membergroupids = ''
WHERE !FIND_IN_SET(userfield.fieldXXXX,'1,2,3')

That basically says clear a user's secondary usergroups, if the user's fieldx is not 1,2 or 3

Again, this is my guess as to what you want, so use at your own risk.

[Colossal31]

Ok what do you not get maybe I can explain it better

[Gio~Logist]

Does my explanation of the code sound as though it's what you're looking for?

[Colossal31]

It sounds like that code will remove all secondary usergroups from a user. That is not what I am looking for. I just need to remove one secondary usergroup from all the users that are in it

[Gio~Logist]

To make it remove just that secondary usergroup, try this:

UPDATE user
LEFT JOIN
userfield on (user.userid = userfield.userid)
SET user.membergroupids = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', user.membergroupids, ','), ',SECONDARYUSERGROUPID,', ','))
WHERE !FIND_IN_SET(userfield.fieldXXXX,'1,2,3')

To be sure that it works the way you want, make a test user that it would effect, then get that user's id and change WHERE !FIND_IN_SET(userfield.fieldXXXX,'1,2,3')to WHERE !FIND_IN_SET(userfield.fieldXXXX,'1,2,3') AND user.userid = USERIDHERE

This way it will only effect that user and you can see if its working correctly :up: