MySQL query month OR

[jmcares]

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!

[Fou-Lu]

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.

[Old Pedant]

Another way to do this:

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.

[jmcares]

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

[Old Pedant]

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:

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

[jmcares]

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!

[Old Pedant]

??? 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:

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.

[jmcares]

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