Help with MySQL Query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • malmazan
    Senior Member
    • Jan 2006
    • 974
    • 4.2.X

    Help with MySQL Query

    Hi all:

    I use custom field 8 for users to select from a list of countries.

    I'm looking to extract the emails of users from a list of countries (say Canada, Brazil and Peru).

    So far I'm able to get a lost for a specific country, but I want to do a number of countries in one go (right now I can do this via the CP, but sometimes I need to do it for 15 or so countries, so it gets really painful) and also I need to exclude those who do not want to receive emails.

    So far:
    PHP Code:
    SELECT email
    FROM user
    LEFT JOIN userfield ON user
    .userid userfield.userid
    WHERE field8 
    'Canada' 
    Many thanks
    Vote for these issues:
  • Andy
    Senior Member
    • Jan 2002
    • 5886
    • 4.1.x

    #2
    Originally posted by malmazan
    but I want to do a number of countries in one go...
    Please be more specific. Do you want to know how to use the OR command in mysql?

    Comment

    • reefland
      Senior Member
      • Sep 2000
      • 1131

      #3
      Maybe try:
      PHP Code:
      SELECT email
      FROM user
      LEFT JOIN userfield ON user
      .userid userfield.userid
      WHERE field8
      IN
      ('Canada','Brazil','Peru'
      sigpic
      Nation of Blue - Kentucky Wildcats Sports


      Some CMS Goodness: Add Avatar to Article

      Comment

      • Andy
        Senior Member
        • Jan 2002
        • 5886
        • 4.1.x

        #4
        Here is what I would do if you want to select more than one country.

        Code:
        SELECT email
        FROM user
        LEFT JOIN userfield ON user.userid = userfield.userid
        WHERE field8 = 'Canada'
        OR field8 = 'Brazil'
        OR field8 = 'Peru'

        Comment

        • reefland
          Senior Member
          • Sep 2000
          • 1131

          #5
          Hey Andy,

          As a student of the languages, is OR more efficient than using IN()?
          sigpic
          Nation of Blue - Kentucky Wildcats Sports


          Some CMS Goodness: Add Avatar to Article

          Comment

          • Andy
            Senior Member
            • Jan 2002
            • 5886
            • 4.1.x

            #6
            Originally posted by reefland
            Hey Andy,

            As a student of the languages, is OR more efficient than using IN()?
            Sorry I don't know. I've only used the OR and didn't even know you can use the IN() option.

            Comment

            • Wayne Luke
              vBulletin Technical Support Lead
              • Aug 2000
              • 73981

              #7
              When the query is compiled by MySQL just before running it, it probably turns the IN() into a series of OR statements. Typically speaking the base SQL language commands will be the most efficient. However any performance lost on using the IN() function is made up in understanding the statement when you pass it on and providing more concise code. You could run both queries with Explain to see which is more efficient though.

              The greatest optimization in the query might be using a subquery and getting rid of the join statement. However the benefits will be minor overall unless you are querying millions of records.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API

              Comment

              • malmazan
                Senior Member
                • Jan 2006
                • 974
                • 4.2.X

                #8
                Both Andy's and reefland's work, thanks. But how do I exclude those who do not want to receive emails (and have the checkmark on not receiving email on their profiles)?
                Vote for these issues:

                Comment

                • Andy
                  Senior Member
                  • Jan 2002
                  • 5886
                  • 4.1.x

                  #9
                  Originally posted by malmazan
                  how do I exclude those who do not want to receive emails (and have the checkmark on not receiving email on their profiles)?
                  Code:
                  SELECT email, options
                  FROM user
                  LEFT JOIN userfield ON user.userid = userfield.userid
                  WHERE field8 = 'Canada'
                  OR field8 = 'Brazil'
                  OR field8 = 'Peru'
                  In your script you can use the variable $options[$i] when the member has a check mark for "Receive Email from Administrators".

                  if ($options == 16) {

                  }

                  Comment

                  • Wayne Luke
                    vBulletin Technical Support Lead
                    • Aug 2000
                    • 73981

                    #10
                    I'd use this query:

                    Code:
                    SELECT user,email
                      FROM user
                      LEFT JOIN userfield ON user.userid = userfield.userid
                      WHERE  user.options & 16 AND userfield.field8 IN('Canada','Brazil','Peru');
                    This selects all users who have the option to receive email from administrators check and have stated they live in one of the three countries listed.
                    Translations provided by Google.

                    Wayne Luke
                    The Rabid Badger - a vBulletin Cloud demonstration site.
                    vBulletin 5 API

                    Comment

                    • Wayne Luke
                      vBulletin Technical Support Lead
                      • Aug 2000
                      • 73981

                      #11
                      Originally posted by Andy
                      In your script you can use the variable $options[$i] when the member has a check mark for "Receive Email from Administrators".

                      if ($options == 16) {

                      }
                      Equal wouldn't work here because the field is used as a bitfield. The value of the field contains all their options an can be much higher than 16 even though they have that value selected. It can also be higher than 16 if they don't.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment

                      • malmazan
                        Senior Member
                        • Jan 2006
                        • 974
                        • 4.2.X

                        #12
                        Originally posted by Wayne Luke
                        I'd use this query:

                        Code:
                        SELECT user,email
                          FROM user
                          LEFT JOIN userfield ON user.userid = userfield.userid
                          WHERE  user.options & 16 AND userfield.field8 IN('Canada','Brazil','Peru');
                        This selects all users who have the option to receive email from administrators check and have stated they live in one of the three countries listed.
                        That gets me an error (error number: 1054 error desc: Unknown column 'user' in 'field list')
                        but taking out the "user" part on the first line does exactly what I need:

                        PHP Code:
                        SELECT email   
                        FROM user
                           LEFT JOIN userfield ON user
                        .userid userfield.userid   
                        WHERE  user
                        .options 16 AND userfield.field8 IN('Canada','Brazil','Peru'); 
                        ¡Many thanks!
                        Vote for these issues:

                        Comment

                        • Wayne Luke
                          vBulletin Technical Support Lead
                          • Aug 2000
                          • 73981

                          #13
                          Had a comma instead of a period. Sorry. Missed that with my pre-coffee eyesight.
                          Translations provided by Google.

                          Wayne Luke
                          The Rabid Badger - a vBulletin Cloud demonstration site.
                          vBulletin 5 API

                          Comment

                          • malmazan
                            Senior Member
                            • Jan 2006
                            • 974
                            • 4.2.X

                            #14
                            Originally posted by Wayne Luke
                            Had a comma instead of a period. Sorry. Missed that with my pre-coffee eyesight.
                            it seems to work with just "email" as well

                            I forgot to add that I need to select from a couple of usergroups (to avoid banned groups and unconfirmed emails).

                            I managed that, though there's probably a more elegant and/or succinct way of doing it:

                            PHP Code:
                            SELECT user.email
                              FROM user
                              LEFT JOIN userfield ON user
                            .userid userfield.userid
                              WHERE  user
                            .options 16 AND userfield.field8
                              IN
                            ('Canada','Brazil','Peru')  AND (usergroupid=OR usergroupid=10 OR usergroupid=11 ); 
                            Vote for these issues:

                            Comment

                            • Andy
                              Senior Member
                              • Jan 2002
                              • 5886
                              • 4.1.x

                              #15
                              Originally posted by Wayne Luke
                              Equal wouldn't work here because the field is used as a bitfield. The value of the field contains all their options an can be much higher than 16 even though they have that value selected. It can also be higher than 16 if they don't.
                              Whoops, it was very early in the morning for me too.

                              I forgot to add the following

                              $options = $options[$i] & 512;
                              if ($options == 16) {

                              }

                              Comment

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