Announcement

Collapse
No announcement yet.

Help with MySQL Query

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

  • Andy
    replied
    Originally posted by Wayne Luke View Post
    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) {

    }

    Leave a comment:


  • malmazan
    replied
    Originally posted by Wayne Luke View Post
    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 ); 

    Leave a comment:


  • Wayne Luke
    replied
    Had a comma instead of a period. Sorry. Missed that with my pre-coffee eyesight.

    Leave a comment:


  • malmazan
    replied
    Originally posted by Wayne Luke View Post
    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!

    Leave a comment:


  • Wayne Luke
    replied
    Originally posted by Andy View Post
    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.

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • Andy
    replied
    Originally posted by malmazan View Post
    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) {

    }

    Leave a comment:


  • malmazan
    replied
    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)?

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • Andy
    replied
    Originally posted by reefland View Post
    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.

    Leave a comment:


  • reefland
    replied
    Hey Andy,

    As a student of the languages, is OR more efficient than using IN()?

    Leave a comment:


  • Andy
    replied
    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'

    Leave a comment:


  • reefland
    replied
    Maybe try:
    PHP Code:
    SELECT email
    FROM user
    LEFT JOIN userfield ON user
    .userid userfield.userid
    WHERE field8
    IN
    ('Canada','Brazil','Peru'

    Leave a comment:


  • Andy
    replied
    Originally posted by malmazan View Post
    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?

    Leave a comment:


  • malmazan
    started a topic Help with MySQL Query

    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
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X