Announcement

Collapse
No announcement yet.

SQL query for spliting users in groups

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query for spliting users in groups

    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.

  • #2
    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.
    Code:
    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.
    Lats...

    Comment


    • #3
      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.
      sigpic
      Nation of Blue - Kentucky Wildcats Sports


      Some CMS Goodness: Add Avatar to Article

      Comment


      • #4
        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?

        Comment


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

          Comment


          • #6
            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?
            My VB forum: BMWRA

            Comment


            • #7
              It would be something like this...
              Code:
              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/showt...a-single-query
              Lats...

              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...
              X