Finding birthday of users.

[Anon]

I was wondering if anyone knows how to detect whether it's a users birtday or not. I have a mysql table with a list of dates of people's birthdays. How can I tell when it's x number of days before a users birthday? I'm thinking along the lines of date_add, but I can't create a working algorithm. Thanks.

[saltwater]

depends on the format you store the birthdays..., I would recommend to store the birth_year, birth_month and birth_day in serperated fields in your table.
en then mysql_query("SELECT name,b_year,b_month,b_day FROM users WHERE b_month='".date('n')."' AND b_day='".date('j')."');

[Anon]

ya, I was thinking about doing it that way. Right now it is stored as a date, but I think it would be much easier if the three were separtate fields.

any other suggestions?? thanks Bruno.

[Anon]

my suggestion is KEEP IT AS A DATE!!!

if you want to do other date manipulation stuff, mysql has everything built in (rtfm), and if you need the month, use MONTH(date), for year use YEAR(date) right in the query and it works fine.

$month = floor(date("m"));
$day = floor(date("d"));
SELECT * FROM people WHERE MONTH(birthday)=$month AND DAYOFMONTH(birthday)=$day

that should give you everyone whose birthday is today.

[Anon]

ya I know how to get it if today is someone's bday, but I want it for the 5 days before someones bday also. That's the more interesting part.

[Anon]

I was thinking something more on the lines of this. Getting the month and day of the birthday and setting the year to the current year. then doing some math between the TO_NUM function ... I think it's to_num. Would this work?

Of course I would have problems with 12/31 etc, but can I create a date with MYSQL statements?

[Anon]

that is just as easy... i actually do the exact thing on my family tree network...
some examples would be kristopeit.com, buehrens.com, pleskac.com and a few others.

it is still heavily under construction, but just updating the main system updates all the sites. these are just my families, and my girlfriends families.

table person
person_id INT
birthday date

<?php
$number_of_days = 7;
$current_day = date("d");
$current_month = date("m");
$current_year = date("Y");
$birthday_query_string = " OR (MONTH(birthday)='".$current_month."' AND DAYOFMONTH(birthday)='".$current_day."'";
for($i=0;$i<$number_of_days;$i++) {
$new_day = strftime("%d",mktime(0,0,0,$current_month, $current_day + 1, $current_year));
$new_month = strftime("%m",mktime(0,0,0,$current_month, $current_day + 1, $current_year));
$new_year = strftime("%Y",mktime(0,0,0,$current_month, $current_day + 1, $current_year));
$current_day = $new_day;
$current_month = $new_month;
$current_year = $new_year;
$birthday_query_string .= " OR (MONTH(birthday)='".$current_month."' AND DAYOFMONTH(birthday)='".$current_day."'";
}
$result = mysql_query("SELECT person_id, birthday FROM person WHERE 0".$birthday_query_string);
while(list($person_id, $birthday) = mysql_fetch_row($result)) {
// $person_id's birthday ($birthday) is within $number_of_days days...
}
?>

i hacked the code a lot, but this should work.

[Anon]

of course you can also do this with later version of mysql like this:

table person
person_id INT
birthday date

"SELECT person_id FROM person WHERE (TO_DAYS(birthday) - TO_DAYS(".date("Y-m-d").")) <= 5"

but i think this will uses more database resources.

[Anon]

I originally thought I could do it with TO_DAYS, but I don't think what you wrote will work. If someone's birthday is on 5/5/79 and today's date is 11/26/2001, you'll never get to be <= 5 unless someone was just born and put into the database.

I will take a look at your previous solution. thanks for your help.

[Anon]

ok here's what I want to do. the birthdays in my db are dates.

SELECT * FROM CALENDAR WHERE TO_DAYS(NOW()) - TO_DAYS(".YEAR(NOW())."-".MONTHS(birthday)."-"DAYS(birthday)." <= 5

this will get it right I think for anything 5 days before a birthday. I can't get the birthday expression to work though.

[Anon]

I did it.

SELECT * FROM CALENDAR WHERE TO_DAYS(concat(year(NOW()),'-',month,'-',day)) - TO_DAYS(NOW()) <= 5 AND TO_DAYS(concat(year(NOW()),'-',month,'-',day)) - TO_DAYS(NOW()) > 0

I'm using separate months and days, but I'm going to change it to dates.

thanks guys.