phpmysqlbetween

php mysql search birthday between two dates


I need to find the birth day of people from the table .. coming in next 7 days from today. I have a query ..SELECT * FROMtableWHEREdobLIKE BETWEEN %-08-17 AND %-08-24 but it returns the records whose dates are not submitted in database..i mean the entry is 0000-00-00 I have stored the birthdates in dates format in table. Please Help me finding the bug.


Solution

  • Since this is mysql, I don't know if DATE_FORMAT() can work on this. But give this a try.

    SELECT * FROM users WHERE DATE_FORMAT(dob, '%c-%d') 
    BETWEEN DATE_FORMAT('1983-08-17', '%c-%d') 
    AND DATE_FORMAT('1983-08-24', '%c-%d') OR (MONTH('1983-08-17') > MONTH('1983-08-24')
    AND (MONTH(dob) >= MONTH('1983-08-17') 
    OR MONTH(dob) <= MONTH('1983-08-24')))
    

    any year can be used (just to complete the date format) since year does not matter

    UPDATE 1

    Tested it on SQLFiddle.com

    SQLFiddle Demo

    UPDATE 2

    I'm sorry for my first answer. I honestly missed to read this line coming in next 7 days from today. And I think that was the reason why I was downvoted by Imre L. He has his point. The reason why I posted the answer like that was because I thought the OP was asking for the days in between regardless of the year. So here is the update.

    SELECT ....
    FROM   ....
    WHERE  DATE(dob) BETWEEN NOW() AND NOW() + INTERVAL 7 DAY
    

    Hope it's clear now. :D