Birthday with where clause

Question

Can any one help me with this

Script

This is what i want to achive

table

id  name    tag_id  status      dob
== ======= ======== ========   ======== 
1   a       1        Married    02/23/1977
2   b       1        Single     02/23/1978
3   c       2        Single     02/23/1960
4   d       1        Married    02/23/1985
5   e       2        Married    02/23/1999
6   f       2        Diovice    02/23/1999
7   g       1        Married    02/28/1999
8   h       2        Married    02/28/1999
9   i       1        Married    03/12/1990

I will love to get the present/ current birthday celebrant with mysql query here is my code:

SELECT * FROM table
WHERE (MONTH(STR_TO_DATE(dob, '%d/%m/%Y'))=MONTH(CURDATE())
    AND (DAY(STR_TO_DATE(dob, '%d/%m/%Y'))=DAY(CURDATE())))
    OR (DAY(LAST_DAY(STR_TO_DATE(dob, '%d/%m/%Y')))=29
    AND (DAY(STR_TO_DATE(bob, '%d/%m/%Y'))=29)
    AND (DAY(LAST_DAY(CURDATE()))=28))
    AND ( STATUS = 'Married' OR STATUS = 'Single' )
    AND tag_id = 1
ORDER BY id ASC

my problem is the above code is just returing all present bithday but what i want is like this:

result:

1   a       1        Married    02/23/1977
2   b       1        Single     02/23/1978
4   d       1        Married    02/23/1985
7   g       1        Married    02/28/1999

Wrong result (produce by the above query):

1   a       1        Married    02/23/1977
2   b       1        Single     02/23/1978
4   d       1        Married    02/23/1985
7   g       1        Married    02/28/1999
8   h       2        Married    02/28/1999

And what i want, i only want Birthday Celebrant for today that/ where is ID = 1. Like i specify with the first result.

the `OR` query is giving you the wrong result. why did you take that exactly? — Sravan, Mar 23 '17 at 06:52
I think what you desire lies within this: `SELECT * FROM table WHERE (MONTH(STR_TO_DATE(dob, '%d/%m/%Y'))=MONTH(CURDATE()) AND (DAY(STR_TO_DATE(dob, '%d/%m/%Y'))=DAY(CURDATE())))` I'm not very sure about the part after 'OR' — Krishnakumar, Mar 23 '17 at 07:01
non of the answer still not working, the or statement is specify and to include the LEAP YEAR Birthday celebrant. — Michael Codes, Mar 23 '17 at 20:00

beejm · Answer 1 · 2017-03-23T07:14:48.810

2

SELECT * FROM table
WHERE MONTH(dob) = MONTH(GETDATE()) AND DAY(dob) = DAY(GETDATE())
and tag_id = 1 and status in ('pending','active')

This should work.

edited Mar 23 '17 at 07:14

answered Mar 23 '17 at 06:40

beejm

2,381
1
10
19

I think this is what he wants. The first part of his query before the 'OR' alone is good to give his desired result. I'm not sure why he goes with the part after the 'OR' statement. – Krishnakumar Mar 23 '17 at 06:58
I am phpmyadmin for mysql so GETDATE() function cannot not be found in PhpMyAdmin and when i change it GETDATE() to NOW() is returning empty result – Michael Codes Mar 23 '17 at 20:03
Thanks for your response – Michael Codes Mar 23 '17 at 20:07
Are you sure that there's someone celebrating their birthday during the time that you ran your script @MichaelCodes – beejm Mar 28 '17 at 02:10
Year i am 100% sure, if there is no birthday celerant i can easy change any users date to current to test my script @BennjoeMordeno – Michael Codes Mar 28 '17 at 07:06
Here is my own method that works for me, please kind affect the changes to this to accept your answer: – Michael Codes Mar 28 '17 at 10:51
SELECT * FROM table WHERE MONTH(STR_TO_DATE(dob, '%d/%m/%Y')) = MONTH(NOW()) AND DAY(STR_TO_DATE(dob, '%d/%m/%Y')) = DAY(NOW()) AND TAG_ID = 2 AND STATUS IN ('single','married') – Michael Codes Mar 28 '17 at 10:51
`SELECT * FROM table WHERE MONTH(STR_TO_DATE(dob, '%d/%m/%Y')) = MONTH(NOW()) AND DAY(STR_TO_DATE(dob, '%d/%m/%Y')) = DAY(NOW()) AND TAG_ID = 2 AND STATUS IN ('single','married')` – Michael Codes Mar 28 '17 at 10:51
@BennjoeMordeno – Michael Codes Mar 28 '17 at 10:51
@MichaelCodes you can answer your own question. Just submit an answer and mark it ad the correct answer. Good for you that you got a working solution! – beejm Mar 28 '17 at 11:18

Sravan · Answer 2 · 2017-03-23T07:19:16.417

0

The OR query is giving you the wrong result.(I am not sure why you have taken)

This is making an or condition for first two queries and remaining ones

So, enclose the OR queries within brackets.

Then, you will get your AND tag_id = 1 executed.

SELECT * FROM table WHERE 
    (MONTH(STR_TO_DATE(dob, '%d/%m/%Y'))=MONTH(CURDATE()) 
AND
    (DAY(STR_TO_DATE(dob, '%d/%m/%Y'))=DAY(CURDATE())))

 OR 

 (
    (DAY(LAST_DAY(STR_TO_DATE(dob, '%d/%m/%Y')))=29 
  AND 
    (DAY(STR_TO_DATE(bob, '%d/%m/%Y'))=29)
  AND
    (DAY(LAST_DAY(CURDATE()))=28))
  )

AND
    ( STATUS = 'ACTIVE' OR STATUS = 'PENDING' )
AND
    tag_id = 1
ORDER BY id ASC

edited Mar 23 '17 at 07:19

answered Mar 23 '17 at 06:54

Sravan

18,467
3
30
54

@Michael Codes, please check my answer. – Sravan Mar 23 '17 at 09:49
I test the code is still not working is still returning both birtday for id 1 and 2, and i only want birthday for with by ID inclusive with LEAP YEAR – Michael Codes Mar 23 '17 at 20:07
Thanks in advance – Michael Codes Mar 23 '17 at 20:07
`OR (DAY(LAST_DAY(STR_TO_DATE(dob, '%d/%m/%Y')))=29 AND (DAY(STR_TO_DATE(bob, '%d/%m/%Y'))=29) AND (DAY(LAST_DAY(CURDATE()))=28))`, what are these for? – Sravan Mar 24 '17 at 05:59
29 and 28 is to check for leap year month that end with 28 and 29 like febuary – Michael Codes Mar 24 '17 at 20:39

score 0 · Accepted Answer · answered Mar 29 '17 at 20:52

This later worked out for me, it might help others and i will love to see more improvement of this answer:

SELECT * FROM table WHERE MONTH(STR_TO_DATE(dob, '%d/%m/%Y')) = MONTH(NOW()) AND DAY(STR_TO_DATE(dob, '%d/%m/%Y')) = DAY(NOW()) AND TAG_ID = 2 AND STATUS IN ('single','married')

Birthday with where clause

3 Answers3