[RESOLVED] Displaying Next Birthday

ecjughead

I am trying to display the next birthday from my DB but I've run into issues. I've tried using a single field (DATE) as well as breaking it down into separate fields (month, day...). I've tried to have the if statement look at each field comparing it to the current date but it fails. It sees that it matches the month (1st field) and says person x has a birthday...but this persons bday was on the 2nd.

I received the following as the first part of a resolution, I just don't completely get the structure:

SELECT dob +
INTERVAL YEAR(CURRENT_DATE)
- YEAR(dob)
YEAR AS birthday_thisyear
FROM daTable

Is there another way or can someone help me with this?

leatherback

the next birthday in general? Or the next birthday of a specific user?

Select * from daTable where birthday_date > now()

i THINK should work to just in general get the next birthday in a table, assuming birthday colum contains a date, datetime or timestamp. (Please correct me if I am wrong guys)

ecjughead

Thank you but this did not work. I'm still trying other ideas but can't seem to get it

ecjughead

$query = "SELECT name 
		FROM birthdays 
		WHERE DAYOFYEAR(dob) >= DAYOFYEAR(CURDATE()) ORDER BY dob DESC";

I got this to work but it sorts only by the birth year and not by month or day. So far, my attempts have yielded no results.

scrupul0us

well you can always stored the birthday as YYYYMMDD, then it will sort exactly as you want

leatherback

Could you please explain a bit clearer with what you would like to get from the database, and under which conditions?

ecjughead

table : id (int-2), name(varchar-30), dob(DATE)

SELECT name, birthday_thisyear + 
         INTERVAL CASE WHEN birthday_thisyear 
                         <= CURRENT_DATE
                       THEN 1 ELSE 0 END 
                    YEAR  AS next_birthday
  FROM (
SELECT name, dob + 
         INTERVAL YEAR(CURRENT_DATE)
                - YEAR(dob) 
             YEAR  AS birthday_thisyear
  FROM {table name} ) AS d1
ORDER BY next_birthday

This is the solution to finding the next upcoming birthday listed in the table.🙂