How to display upcoming birthdays?

[scorptique]

Hello again guys, I need some help here.

I have a list of contact details, which includes their birthdays. If user clicks the link "Sort by birthday", the url will become contacts.php?category=cl_dob. So I use the GET method and put it in $category. How do I sort the contacts by the upcoming birthdays? Means the contact with the nearest birthday will be at the top.


I tried this but it didnt work. Help guys.

if ($category == 'cl_dob')
{ 
$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob from contact_list 
where username = '$username'  
AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')
ORDER BY cl_dob DESC ";
} 

[kuau]

This is probably way off, but it seems to me that you would have to create a temporary field (AS) that holds the number of days between NOW () and the dob, and then sort by that field ascending to get the nearest birthdays on top.

[scorptique]

haha. well, your method will take into consideration the year.
So let's say A's bday is on the 24 Dec 1985 while B's bday is on the 23 Dec 1983, by following your method, the nearest bday at the top will be 24 Dec 1985. This is because of his year.
But Im trying to search for upcoming birthdays, means it does not take the 'year' into consideration.

[kuau]

That is an incorrect assumption. I meant to create a temporary field NOT using the year, rather using a format that converts the date to the day number of the year (ie. 0-365, or "z" in php or %j in MySQL), but I am up against a deadline and do not have time to think it through, so I apologize for offering a suggestion.