RE: [PHP-DB] Next birthday?

On Fri, 8 Mar 2002, Robert V. Zwink wrote: > 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. Not for me. I think it should be ordered like this: ORDER BY DAYOFYEAR(member_dob) ASC Otherwise it will display the person who was born on 25th of March 1988 instead of the one born 11th of March 1980. Pls. correct me if I am wrong. Thanks & Cheers, Kristjan P.S. Pls. CC to kristk@ut.ee - I am only on the digest. > 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 >