Birthday Notifications

[JamesNZ]

On my website I'm wanting to list birthday's within the next seven days.

I have players named listed in my database as follows:

Ashley Giles 1973-03-19
Reon King 1975-10-06
Inzamam-ul-haq 1970-03-03

Can anyone give me some tips on how best to do this?

[visualAd]

You need to do these things, all of which can be done in a MySql query:

Find this years birthday.
Convert todays date to days.
Convert this years birthday to days.
Check the difference is between 0 and 7


SELECT datefield FROM table_name WHERE
TO_DAYS(CONCAT('2005-',
MONTH(datefield), '-',
DAYOFMONTH(datefield))) -
TO_DAYS(CURDATE()) BETWEEN 0 AND 7;

The part CONCAT('2005-', MONTH(datefield), '-', DAYOFMONTH(datefield)) is used to construct a date with this years birthday.

[JamesNZ]

Thanks very much visualAd - worked a treat :)

[The Hobo]

I haven't tried it, but would something like this also work?

$sql = "SELECT * FROM tbl WHERE birthdate < '" . date('Y-m-d', strtotime("+7 days")) . "'";

[JamesNZ]

Slight problem with what I'm using:

$sql = " SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%D %b %Y') DOB, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

It displays in the following format:

Upcoming Birthdays in the next 7 Days...

Albie Morkel
10th Jun 1981 (Age: 24)
Daryl Tuffey
11th Jun 1978 (Age: 27)
Alex Wharf
4th Jun 1975 (Age: 30)
Thilina Kandamby
4th Jun 1982 (Age: 23)
Mervyn Dillon
5th Jun 1974 (Age: 31)
Andy Symonds
9th Jun 1975 (Age: 30)

when it should really be:

Upcoming Birthdays in the next 7 Days...

Alex Wharf
4th Jun 1975 (Age: 30)
Thilina Kandamby
4th Jun 1982 (Age: 23)
Mervyn Dillon
5th Jun 1974 (Age: 31)
Andy Symonds
9th Jun 1975 (Age: 30)
Albie Morkel
10th Jun 1981 (Age: 24)
Daryl Tuffey
11th Jun 1978 (Age: 27)

Any tips?

[visualAd]

You need to add an ORDER BY to the end of the query.

ORDER BY DOB ASC;

[JamesNZ]

Thanks for responding, same result however with it displaying like this:

Albie Morkel
10th Jun 1981 (Age: 24)
Daryl Tuffey
11th Jun 1978 (Age: 27)
Alex Wharf
4th Jun 1975 (Age: 30)
Thilina Kandamby
4th Jun 1982 (Age: 23)
Mervyn Dillon
5th Jun 1974 (Age: 31)
Andy Symonds
9th Jun 1975 (Age: 30)

[visualAd]

You need to call the DOB column something else. It is using the alias to order the results. If you call you DOB column something different then it should use the correct one:

DATE_FORMAT(DOB,'%D %b %Y') birthday,

[JamesNZ]

Still doesn't work unforunately :(

Refer to attached. Ahh, I see what it's doing. It's sorting by year when I really want it to sort by month/day. Is this possible?

[visualAd]

Well its sorting by DOB so that is correct. Am I right in thinking you want to sort by just the month and the day of the birthday? - If so you will need to add an extra column which contains this years birthday and sort using that:

... Birthday,
CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) DOB2, TopPlayer ..
..
ORDER BY DOB2

[JamesNZ]

Cheers visualad - will go about it that way :)

[JamesNZ]

Know of any quick way of going through and adding another field with this years birthday visualad?

I was thinking of running a loop through all the dob's in the table and removing the first 4 characters off the DOB and replacing it with 2005 before updating a new column with the modified DOB. How would I remove the first four characters from a date in PHP?

[visualAd]

Know of any quick way of going through and adding another field with this years birthday visualad?

I was thinking of running a loop through all the dob's in the table and removing the first 4 characters off the DOB and replacing it with 2005 before updating a new column with the modified DOB. How would I remove the first four characters from a date in PHP?
If you add a column to store this years birthday you are faced with following issues:

Extra space required to store the other birthday.
Having to calculate this years birthday before storing the new user.
Having to update all the users once a year.

The field you need can be calculated using the date of birth as I outlined above and there really is no need to add an extra field for this years birthday.

[JamesNZ]

Thanks for that. Are you referring to having the query structured like this:

$sql = " SELECT PlayerIDAuto, PlayerName, CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) DOB2 TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB2 ASC";

[JamesNZ]

Yes I presume it is, all working fine with the following:

$sql = " SELECT PlayerIDAuto, PlayerName, CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) DOB2, DATE_FORMAT(DOB,'%D %b %Y') Birthday, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB2 ASC";

Thanks so much for your help :)

[visualAd]

No problems - don't forget to resolve the thread. ;)

Thanks

[JamesNZ]

You must spread some Reputation around before giving it to visualAd again.

How stupid is that :D

Sure :)

[visualAd]

No no - I mean change the icon to a tick, you don't have to rate the post. But thanks for trying anyway :thumb:

[JamesNZ]

No problem - thanks again for your help :)

[JamesNZ]

Isn't working again visualad :( - can you help?


Upcoming Birthdays in the next 7 Days...

Scott Styris
10th Jul 1975 (Age: 30)
Harbhajan Singh
3rd Jul 1980 (Age: 25)
Zander de Bruyn
5th Jul 1975 (Age: 30)
Makhaya Ntini
6th Jul 1977 (Age: 28)
Douglas Hondo
7th Jul 1979 (Age: 26)
Mahendra Singh Dhoni
7th Jul 1981 (Age: 24)
Sourav Ganguly
8th Jul 1972 (Age: 33)
Ian Bradshaw
9th Jul 1974 (Age: 31)


Query:

$sql = " SELECT PlayerIDAuto, PlayerName, CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) DOB2, DATE_FORMAT(DOB,'%D %b %Y') Birthday, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB2 ASC";

[visualAd]

I think it is interpteting it as a string. Try this:

CAST(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) AS DATE) DOB2

[JamesNZ]

Thanks for responding but it didn't work unfortnately :(

$sql = " SELECT PlayerIDAuto, PlayerName, CAST(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB)) AS DATE) DOB2, DATE_FORMAT(DOB,'%D %b %Y') Birthday, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS Age, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB2 ASC";

[visualAd]

Can you post the output?

[JamesNZ]

Same output as before actually:

Scott Styris
10th Jul 1975 (Age: 30)
Harbhajan Singh
3rd Jul 1980 (Age: 25)
Zander de Bruyn
5th Jul 1975 (Age: 30)
Makhaya Ntini
6th Jul 1977 (Age: 28)
Douglas Hondo
7th Jul 1979 (Age: 26)
Mahendra Singh Dhoni
7th Jul 1981 (Age: 24)
Sourav Ganguly
8th Jul 1972 (Age: 33)
Ian Bradshaw
9th Jul 1974 (Age: 31)

[visualAd]

I've tested this on my SQL server and it works fine. I'm not sure why you are getting different results :confused:

[JamesNZ]

Any difference with MySQL?

[CornedBee]

Does MySQL support DATE_FORMAT?

What version of MySQL? CAST is supported starting with 4.0.2, but only in 4.1.0 and later does a cast to DATE affect the way the value is interpreted.

[JamesNZ]

My web host has:

MySQL version 4.0.24-standard-log

[visualAd]

If the function is not supported you'll get an SQL error. Have you tried the query in PHPMyAdmin? Can you post the full query results from there?

[JamesNZ]

Sure - attached.

[visualAd]

Try Cast AS DATETIME too and see what you get.

[CornedBee]

I'm telling you, the ordering doesn't work because the 4.0.x implementation of CAST is incomplete.

Try this:
$sql = " SELECT PlayerIDAuto, PlayerName, ".
"CAST(CONCAT('2005-', LPAD(MONTH(DOB), 2, '0'), '-', LPAD(DAYOFMONTH(DOB), 2, '0')) AS DATE) DOB2, ".
"DATE_FORMAT(DOB,'%D %b %Y') Birthday, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS Age, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB2 ASC";

[visualAd]

Thanks for the correction there. I didn't read that post you made :rolleyes:, it make sense now.

[JamesNZ]

Thanks a lot guys - really appreciate your help :)

That worked a treat :)