I need help with mySQL commands - make user a secondary usergroup member

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • criscokid
    Senior Member
    • Jun 2003
    • 315
    • 3.6.x

    I need help with mySQL commands - make user a secondary usergroup member

    I need help with mySQL commands to achieve the following:

    Search for users with Profile field16 = 'United Kingdom' then make each of those users a secondary usergroup member of Usergroup ID 17.

    Many thanks in advance.
  • Jake Bunce
    Senior Member
    • Dec 2000
    • 46598
    • 3.6.x

    #2
    I don't know the SQL to manipulate a comma list (the membergroupids field) within the query itself. If no one else here knows then you can try www.vbulletin.org.

    I recommend writing a small PHP script to do this since it is very easy to work with comma lists using array functions (also a www.vbulletin.org question).

    Comment

    • Lats
      Senior Member
      • Mar 2002
      • 3671

      #3
      Try these 2 queries...
      Code:
      UPDATE
          user 
      SET 
          membergroupids = CONCAT(membergroupids, ',17')
      WHERE 
          userfield.field16 = 'United Kingdom'
      AND
          membergroupids != '';
      
      UPDATE 
          user 
      SET 
          membergroupids = '17'
      WHERE
          userfield.field16 = 'United Kingdom'
      AND
          membergroupids = '';
      Lats...

      Comment

      • criscokid
        Senior Member
        • Jun 2003
        • 315
        • 3.6.x

        #4
        The first query returns:

        error desc: Unknown table 'userfield' in where clause

        Comment

        • Lats
          Senior Member
          • Mar 2002
          • 3671

          #5
          If you're using prefixes, just add it to the table names.
          Lats...

          Comment

          • criscokid
            Senior Member
            • Jun 2003
            • 315
            • 3.6.x

            #6
            I'm not using any prefixes and the userfield table does exists.

            I'm wondering if the mySQL command is correct... I previously tried to run a mySQL command to seta user a secondary usergroup. If you take a look at that thread you'll see that the format of the command is different.

            Another question:

            Is userfield.field16 = 'United Kingdom' correct? I'm hoping that it won't try and modify records where the person's counry is 'United States of America'.

            Comment

            • Lats
              Senior Member
              • Mar 2002
              • 3671

              #7
              My apologies, I left out a join...
              Code:
              UPDATE
                  user
              LEFT JOIN
                  userfield
              ON
                  user.userid = userfield.userid 
              SET 
                  membergroupids = CONCAT(membergroupids, ',17')
              WHERE 
                  userfield.field16 = 'United Kingdom'
              AND
                  membergroupids != '';
              The United Kingdon bit is correct.
              Lats...

              Comment

              • Lats
                Senior Member
                • Mar 2002
                • 3671

                #8
                ...and the other one...
                Code:
                UPDATE 
                    user 
                LEFT JOIN
                    userfield
                ON
                    user.userid = userfield.userid 
                SET 
                    membergroupids = '17'
                WHERE
                    userfield.field16 = 'United Kingdom'
                AND
                    membergroupids = '';
                Lats...

                Comment

                • criscokid
                  Senior Member
                  • Jun 2003
                  • 315
                  • 3.6.x

                  #9
                  Thanks Lats - that did the trick

                  Would it be possible to run the above script but have it exclude 'usergroup 12'?
                  Last edited by criscokid; Wed 25 Oct '06, 8:02am.

                  Comment

                  • Lats
                    Senior Member
                    • Mar 2002
                    • 3671

                    #10
                    Yes, add this to the end...
                    Code:
                    AND
                       user.usergroupid != 12
                    Lats...

                    Comment

                    • criscokid
                      Senior Member
                      • Jun 2003
                      • 315
                      • 3.6.x

                      #11
                      Sorry to be a pain... I did try work it out for myself but couldn't get the syntax right. What if I wanted to exclude 'usergroup 12' and 'usergroup 13'?

                      Comment

                      • Marco van Herwaarden
                        Senior Member
                        • Nov 2004
                        • 6999
                        • 3.8.x

                        #12
                        Code:
                        [LEFT]AND
                           user.usergroupid NOT IN (12, 13)[/LEFT]
                        Want to take your board beyond the standard vBulletin features?
                        Visit the official Member to Member support site for vBulletin Modifications: www.vbulletin.org

                        Comment

                        • criscokid
                          Senior Member
                          • Jun 2003
                          • 315
                          • 3.6.x

                          #13
                          That results in:

                          Error number 1064
                          error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
                          AND
                          user.usergroupid NOT IN (12, 13)' at line 12

                          Comment

                          • Lats
                            Senior Member
                            • Mar 2002
                            • 3671

                            #14
                            You need to take out the semi-colon ( from the previous bit, so...
                            Code:
                            UPDATE
                                user
                            LEFT JOIN
                                userfield
                            ON
                                user.userid = userfield.userid 
                            SET 
                                membergroupids = CONCAT(membergroupids, ',17')
                            WHERE 
                                userfield.field16 = 'United Kingdom'
                            AND
                                membergroupids != ''
                            AND
                               user.usergroupid NOT IN (12, 13)
                            Lats...

                            Comment

                            • criscokid
                              Senior Member
                              • Jun 2003
                              • 315
                              • 3.6.x

                              #15
                              Does the second query need changing or does that stay the same?

                              Comment

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