RE: [PHP-DB] Next birthday?

The simple answer might be: SELECT member.* FROM member WHERE DAYOFYEAR(member_dob) >= DAYOFYEAR(CURDATE()) ORDER BY member_dob DESC LIMIT 1 Seems to work for me. The problem is that it wouldn't support members that have a birthday on the same day :) To solve that I would select the next birthday from the 'member' table, then go back and select all the members that have that birthday. It also would not support selecting January birthdays in December (which might be a problem Dec 31). so you would actually need three queries: SELECT member_dob FROM member WHERE DAYOFYEAR(member_dob) >= DAYOFYEAR(CURDATE()) ORDER BY member_dob DESC LIMIT 1 if that returns 0 results, then you would likely be in December trying to show the next birthday in January. Go back to the database starting with January 1 and select brithdays starting at the begining of the year. SELECT member_dob FROM member WHERE DAYOFYEAR(member_dob) >= 0 ORDER BY member_dob DESC LIMIT 1 then take that result and: SELECT member.* FROM member WHERE member_dob = '$result_from_previous_query' ORDER BY last_name This really seems like overkill. This query would be much simpler if you just showed everyone's birthday next month (or something like that). Robert Zwink http://www.zwink.net/daid.php -----Original Message----- From: Kristjan Kanarik [mailto:kristk@ut.ee] Sent: Friday, March 08, 2002 5:30 AM To: php-db@lists.php.net Subject: [PHP-DB] Next birthday? I have a table called 'members' with a field (among many others) 'member_dob' - the birthday of a particular member. The data type of this field is date (YYYY-mm-dd). What I'd like to do now is to fetch one particular row from the database (I am using MySQL 3.23.37) - namely the row of the member who is the next one to have a birthday. Can this be done only with a advanced query or should I fetch all rows and use PHP to find out who is the next one to have a birthday? I'd prefer letting MySQL to do the job... Any ideas? TIA, Kristjan P.S. Pls. CC to kristk@ut.ee as well - I am only on the digest. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php