Announcement

Collapse
No announcement yet.

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

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

  • 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.

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


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


      • #4
        The first query returns:

        error desc: Unknown table 'userfield' in where clause

        Comment


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

          Comment


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


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


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


                • #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 25th Oct '06, 9:02am.

                  Comment


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

                    Comment


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


                      • #12
                        Code:
                        AND user.usergroupid NOT IN (12, 13)
                        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


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


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


                            • #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...
                              X