On the front page of Mullenweg.com I’ve been using a query that grabs all the birthdays for that month and displays their information, ordered by the day of the month. Here is the query I’ve been using:
SELECT id, first, middle, last, private, dod, DATE_FORMAT(dob, '%M %e, %Y') as nicedob, DATE_FORMAT(dob, '%M, %Y') as mob, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS age, DATE_FORMAT(dob, '%M %e') as day, DATE_FORMAT(NOW(), '%M %e') as today FROM people WHERE MONTH(NOW()) = MONTH(dob) ORDER BY DATE_FORMAT(dob,'%d')
This has a couple of problems: first, it has a bunch of junk in there that I’m not using anymore and/or should be done in the PHP, not the SQL. More fundementally is the problem that it reports birthdays incorrectly! My sister Charleen pointed out that the front page had the date of her birthday, July 21st, right, but it was going to be her 28th birthday and not her 27th. The problem was the code was calculating how old she is today and then reporting that. The column is even being called ‘age’ because I took the code from another part of the site where I was calculating age. Obviously some changes were needed. I started by removing the stuff I don’t use anymore, and making it a little tighter:
SELECT id, first, middle, last, dod, DATE_FORMAT(dob, '%M %e, %Y') as nicedob, YEAR(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob))) AS birthday FROM people WHERE MONTH(NOW()) = MONTH(dob) ORDER BY DATE_FORMAT(dob,'%d');
Now we have a much more managable chunk of SQL, but still nothing to fix the problem. After much deliberation, and thinking about ways to implement a conditional statement to check whether the day had passed yet and then add a year to the ‘birthday’ column if required, but after a while I just realized that the simplest and most accurate solution would be to just subtract the year of birth from this year. This has no issues since the year never changes in the middle of a month. Here’s the final product:
SELECT id, first, middle, last, dod, DATE_FORMAT(dob, '%M %e, %Y') as nicedob, YEAR(NOW())-YEAR(dob) AS birthday FROM people WHERE MONTH(NOW()) = MONTH(dob) ORDER BY DAYOFMONTH(dob);
Ahhh that feels good :).