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
