| | Comments (0)
I was actually online to have a look at an old script and bit of SQL code which stopped working when I transferred my hosting off Godzilla a couple of years ago.  I took a while to remember the sql password and figure out what I needed to change to access MySQL on the new host.  Finally got the address book scripts to work, but the birthday script wasn't ordering correctly - it was ordering by item key, not what I'd said to order by.  Figured it out tonight - it had some extra ' characters that it didn't need.

Check this out:

  $SQL="select id, firstname, lastname, birthday, month(birthday), monthname(birthday), year(birthday), dayofmonth(birthday), ceiling((TO_DAYS(NOW())-TO_DAYS(birthday))/365.25) AS agenext from addressbook where ceiling((TO_DAYS(NOW())-TO_DAYS(birthday))/365.25) is not null order by month(birthday), dayofmonth(birthday);";

So it pulls out a bunch of data from a database including the person's name and their birthday day/month/year.  Then it figures out the age they are turning next by looking at the date now and the date of their birthday and dividing by the number of days in the year.  The .25 on the end is important, as MySQL uses this for years, and the calculations could be out by a day on the day of the person's birthday if it's wrong.  Then it orders it chronologically.  Perl is used to put it all together into a neat table which has the person, their birthday, and their age next birthday in a table, ordered correctly, on the fly.  

I'm sure there's probably better, less hacky ways of doing it, but I was pretty impressed with it at the time (feel free to put down how you'd do it in the comments :) )

Leave a comment

Kazza's "Boring Life Of a Geek" aka BLOG

IT geek, originally from Sydney, moved to Canberra in 2007. Married to "the sweetie", aka Stu. Prolific photographer, Lego junkie and tropical fish keeper.

Kazza the Blank One home