Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    MySQL query month OR

    Hi Everyone,

    Can anyone knows about the OR where clause about the month.
    Please see my where clause code below:

    WHERE
    MONTH(contacts.`birthdate`) = '$MONTH' or MONTH(contacts_cstm.`anniversary_c`) = '$MONTH' or MONTH(contacts_cstm.`spousebirthdate_c`) = '$MONTH'
    and contacts.id = contacts_cstm.id_c
    and contacts_cstm.donor_giving_level_c = '$DONOR_CATEGORY'

    Is there a way to make this right?

    Thanks in advance!

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This is the wrong forum. Moving to mysql.
    Add brackets around the group for the OR. AND has a higher priority than OR, so this match will return any records where the birthdate month IS month OR anniversary_c month IS month, OR (spoudsebirthdate_c month IS month AND contacts.id = contacts_cstm.id_c AND contacts_cstm.doner_giving_level_c = DONOR_CATEGORY). Explicit brackets should be added to group the OR's together to apply the AND to the result of the OR's.

    MONTH() returns an integer. You should compare the $MONTH as an integer and not a string. Most MySQL configurations will be lenient and cast that for you though, but there is no guarantee that it will. Also, the first AND condition seems that it should be a join condition, and not that of a WHERE condition.

  • Users who have thanked Fou-Lu for this post:

    jmcares (04-19-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Another way to do this:
    Code:
    WHERE $MONTH in (contacts.birthdate,contacts_cstm.anniversary_c,contacts_cstm.spousebirthdate_c)
    and contacts.id = contacts_cstm.id_c
    and contacts_cstm.donor_giving_level_c = '$DONOR_CATEGORY'
    People forget that IN ( ) works both ways: You can have a db field that is in a set of values or you can have a value that is in a set of fields. Or a field in a set of fields. Or.... Well, most any mixture, really.

    And there is no reason at all to use `...` around any of those field names. There is no way that any of those are MySQL keywords.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jmcares (04-19-2012)

  • #4
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Month query format

    Thanks for all your reply

    my direct input parameter is '$MONTH'


    SELECT
    DATE_FORMAT(contacts.`birthdate`, '%M %d') as 'BIRTHDATE',
    DATE_FORMAT(contacts_cstm.`anniversary_c`, '%M %d') as 'ANNIVERSARIES',
    DATE_FORMAT(contacts_cstm.`spousebirthdate_c`, '%M %d') as 'SPOUSE BIRTHDAY',
    UPPER(contacts.`salutation`) as 'SAL',
    CONCAT(IFNULL(first_name," ")," ",IFNULL(middle_name_c,"")," ",IFNULL(last_name,""))as 'NAME',
    contacts.`last_name` as 'LASTNAME',
    contacts.`first_name` as 'FIRSTNAME',
    contacts_cstm.`middle_name_c` as 'MI',
    contacts_cstm.`nick_name_c` as 'NICK NAME',
    contacts_cstm.`contactperson_c` as 'CONTACT PERSON',
    contacts_cstm.`shipping_address_street_c` as 'Street',
    contacts_cstm.`barangay_c` as 'BRGY.',
    contacts_cstm.`city_c` as 'CITY',
    contacts_cstm.`shipping_address_postalcode_c` as 'Zip Code',
    contacts_cstm.`donor_giving_level_c` as 'CAT',
    contacts_cstm.`sub_cat_c` as 'SUB CAT',
    CONCAT(IFNULL(phone_home,"")," ",IFNULL(phone_mobile,"")," ",IFNULL(phone_work,"")," ",IFNULL(phone_other,""))as 'CONTACT NO'
    FROM
    `contacts` contacts,
    `contacts_cstm` contacts_cstm
    WHERE
    contacts.id = contacts_cstm.id_c
    and MONTH (contacts.birthdate = '$BIRTHDAY' OR contacts_cstm.anniversary_c = '$ANNIVERSARY' OR contacts_cstm.spousebirthdate_c = '$SPOUSEBIRTHDAY' )
    and contacts_cstm.donor_giving_level_c = '$DONOR_CATEGORY'
    ORDER BY
    contacts.birthdate, contacts_cstm.anniversary_c, contacts_cstm.spousebirthdate_c ASC


    I did'nt resolve so far. thanks, for the help

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    You didn't resolve it because you misread FouLu's answer.

    You need a separate MONTH() function for each field.

    You need parentheses around the *ENTIRE SET* of MONTH() tests.

    Thus:
    Code:
    ...
    WHERE
        contacts.id = contacts_cstm.id_c
    AND (    MONTH (contacts.birthdate) = $BIRTHDAY 
          OR MONTH(contacts_cstm.anniversary_c) = $ANNIVERSARY 
          OR MONTH(contacts_cstm.spousebirthdate_c) = $SPOUSEBIRTHDAY
        )
    AND contacts_cstm.donor_giving_level_c = '$DONOR_CATEGORY'
    ...
    MONTH() produces an INTEGER (a number) so it should be compared to a number. So you should NOT put apostrophes around '$BIRTHDAY', etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jmcares (04-20-2012)

  • #6
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    report result

    Hi to All,

    Thanks Old Pedant.

    I got this result all i want is to view all only my selection month I input my paramater as 4 then all this show up. It should April only please help me.

    BIRTHDATE ANNIVERSARIES SPOUSE BIRTHDAY
    April 2 January 17 December 24
    April 6 October 22 December 12
    April 15 December 25
    April 20 March 14 May 19
    April 25 Decemebr 25 July 29
    July 30 April 20 May 29

    Thanks in advance!

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    ??? I don't understand.

    EVERY ONE of those records has ONE of the events in April.

    So it is indeed selecting the correct records.

    Do you mean you only want to *DISPLAY* the word "APRIL"???

    If so, you don't even need to pay attention to the query results. You *KNOW* that the query will only get you records where one of the dates is in April, so just put "APRIL" at the top of your page and you are done.

    But in any case, it is *YOUR CODE* that is showing the month and day:
    Code:
    SELECT
    DATE_FORMAT(contacts.`birthdate`, '%M %d') as 'BIRTHDATE',
    DATE_FORMAT(contacts_cstm.`anniversary_c`, '%M %d') as 'ANNIVERSARIES',
    DATE_FORMAT(contacts_cstm.`spousebirthdate_c`, '%M %d') as 'SPOUSE BIRTHDAY',
    ,,,
    You are TELLING MySQL that you want the date in that format! If you want a different format, then change the '%M %d' to what you want.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jmcares (05-08-2012)

  • #8
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Thanks Everyone for the help:)

    I figure it out the codes.

    just to share here's the query:

    SELECT
    CASE WHEN MONTH(contacts.`birthdate`) = '$MONTH' THEN DATE_FORMAT(contacts.`birthdate`, '%M %d') ELSE NULL END as 'BIRTHDATE',
    CASE WHEN MONTH(contacts_cstm.`anniversary_c`) = '$MONTH' THEN DATE_FORMAT(contacts_cstm.`anniversary_c`, '%M %d') ELSE NULL END as 'ANNIVERSARIES',
    CASE WHEN MONTH(contacts_cstm.`spousebirthdate_c`) = '$MONTH' THEN DATE_FORMAT(contacts_cstm.`spousebirthdate_c`, '%M %d') ELSE NULL END as 'SPOUSE BIRTHDAY',
    UPPER(contacts.`salutation`) as 'SAL',
    CONCAT(IFNULL(first_name," ")," ",IFNULL(middle_name_c,"")," ",IFNULL(last_name,""))as 'NAME',
    contacts_cstm.`spouse_name_c` as 'SPOUSE NAME',
    contacts.`last_name` as 'LASTNAME',
    contacts.`first_name` as 'FIRSTNAME',
    contacts_cstm.`middle_name_c` as 'MI',
    contacts_cstm.`nick_name_c` as 'NICK NAME',
    contacts_cstm.`contactperson_c` as 'CONTACT PERSON',
    contacts_cstm.`shipping_address_street_c` as 'Street',
    contacts_cstm.`barangay_c` as 'BRGY.',
    contacts_cstm.`city_c` as 'CITY',
    contacts_cstm.`shipping_address_postalcode_c` as 'Zip Code',
    contacts_cstm.`donor_giving_level_c` as 'CAT',
    contacts_cstm.`sub_cat_c` as 'SUB CAT',
    CONCAT(IFNULL(phone_home,"")," ",IFNULL(phone_mobile,"")," ",IFNULL(phone_work,"")," ",IFNULL(phone_other,""))as 'CONTACT NO'
    FROM
    `contacts` contacts,
    `contacts_cstm` contacts_cstm
    WHERE
    ( MONTH(contacts.birthdate) = '$MONTH'
    OR MONTH(contacts_cstm.anniversary_c) = '$MONTH'
    OR MONTH(contacts_cstm.spousebirthdate_c) = '$MONTH' )
    and contacts.id = contacts_cstm.id_c
    and contacts_cstm.donor_giving_level_c = '$DONOR_CATEGORY'
    GROUP BY
    contacts.id
    ORDER BY
    MONTH(contacts.birthdate) ASC


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •