How to show upcoming birthdays?

Question

I know that I am not the first to ask how to display upcoming birthdays. But, unfortunately, all the other answers did not help me figure out how to do this.

I have a database with the following structure:

Id    -    Birthday
1          9/14/1996
2          8/27/1990
--         --

As you can see, the date of birth is saved in the "Birthday" field. And I would like to show you five upcoming birthdays (including birthdays today).

But how can I do this? I tried this, but this did not work:

    $query="SELECT Id 
    FROM participants
    WHERE DATE_ADD(STR_TO_DATE(birthday, %n/%j/%Y), INTERVAL YEAR(CURDATE())-YEAR(STR_TO_DATE(birthday, %n/%j/%Y)) YEAR)";

Thanks for the help!

Answer 1

The key to this is order of things, not use where. This prevents the problem at the end of the year.

, , , MM-DD , :

select p.*
from participants
order by (case when date_format(STR_TO_DATE(birthday, %n/%j/%Y), '%m-%d') >= date_format(now(), '%m-%d')
               then 0 else 1
          end),
         date_format(STR_TO_DATE(birthday, %n/%j/%Y), '%m-%d')
limit 5;

EDIT:

:

select p.*
from participants
order by (case when date_format(birthdate, '%m-%d') >= date_format(now(), '%m-%d')
               then 0 else 1
          end),
         date_format(birthdate, '%m-%d')
limit 5;

Answer 2

select top 5 * 
from myTable 
where 
      month(dob) >= month(now()) 
  and day(dob) >= day(now()) 
order by dob asc