Getting Upcoming Birthdays From MySQL

Assuming you have a table called users with birthday as bday for the column name, this will pull all users who are having a birthday in the next 7 days. This is very useful if you want to showcase your member’s birthdays on your website.

There is of course one small problem with this, it’s not sorted. If we sort by bday ASC it’ll still be off since user’s are born on different years. What we really want is to sort by month and day only. I’m sure this can be done in MySQL but here is a PHP solution to take care of it.

Using usort() we can sort based on the month and day. You now have your list sorted :)

, , ,

  • http://smart-sites.ru eldar

    Awesome! You solved my problem! Thanx!

  • ptlyx

    I think this will not work at the end of the year, because DAYOFYEAR() will return something between 1 and 366.

  • ptx

    this will not work at the end of the year and may return strange results at midnight

  • SimonSimCity

    Hi, all

    This post is at least one year old but I think I’ve got the solution all people in the web are looking for 😉
    The second time it looks so simple ….

    My solution: I created a temporary date using the day and the month of the birthday and used the year of the current date. So I can use date_diff 😉
    I’ve also resolved the problem of the end of the year.
    http://stackoverflow.com/questions/2224404/mysql-birthday-reminder-leap-year/4940425#4940425

  • http://www.mattlevavi.com/ Matt Levavi

    Your query kept returning two dates (using “Today”  / +0) when I tried to use it dynamically as part of a “today, next week, next 30 days, etc” system

    I put this together after trying yours:

    SELECT First_name, Last_name, DATE_FORMAT(DOB, ‘%m-%d’) AS Bday
    FROM table
    WHERE DATE_FORMAT(DOB, ‘%m-%d’) BETWEEN
    DATE_FORMAT( curdate(), ‘%m-%d’) AND
    DATE_FORMAT( curdate() + INTERVAL $NUMBER DAY, ‘%m-%d’)
    ORDER BY Bday,First_name ASC
     
    works with $NUMBER  being 0, 7, 30, 60, etc.

  • Mark Ablov

    > What we really want is to sort by month and day only. I’m sure this can be done in MySQL

    User “order by DAYOFYEAR(bday)” :)

  • Yousuf

    Thank man! You’re the man.

Powered by WordPress. Designed by Woo Themes