[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 (-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 [EMAIL PROTECTED] 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
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:[EMAIL PROTECTED]] Sent: Friday, March 08, 2002 5:30 AM To: [EMAIL PROTECTED] 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 (-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 [EMAIL PROTECTED] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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 [EMAIL PROTECTED] - 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:[EMAIL PROTECTED]] Sent: Friday, March 08, 2002 5:30 AM To: [EMAIL PROTECTED] 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 (-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 [EMAIL PROTECTED] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
