Displaying Birthdays : How to show Entries with only Current / Future Dates?

4 posts by 2 authors in: Forums > CMS Builder
Last Post: February 17, 2010   (RSS)

By Keltam2 - February 16, 2010

Hi there!
I have a database of members in a directory, and I'm looking to display members with the three closest birthdays on a sidebar (see attached screenshot). I'm not sure how to compare today's date to the birthdays in the database - I have it displaying birthdays in date order currently, and the list begins with January 1.

<?php

require_once "/home/kzoopeak/public_html/cmsAdmin/lib/viewer_functions.php"; list($member_directoryRecords, $member_directoryMetaData) = getRecords(array(
'tableName' => 'member_directory',
'limit' => '3',
'orderBy' => 'birthday ASC',
));?>


Is there an easy way to have it compare the birthday against today's date, and only display current or future birthdays? Thanks so much for your help!!
Kelly

Re: [Keltam2] Displaying Birthdays : How to show Entries with only Current / Future Dates?

By Dave - February 17, 2010

Hi Kelly,

Sorting by birthday is actually a little bit tricky. Here's some code that uses the new advanced 'addSelectExpr' option from v2.01. Try this:

<?php

require_once "/home/kzoopeak/public_html/cmsAdmin/lib/viewer_functions.php";

list($member_directoryRecords, $member_directoryMetaData) = getRecords(array(
'tableName' => 'member_directory',
'addSelectExpr' => "IF( ### create sortable field for birthdays that lists Jan 1st after Dec 31st,
DAYOFYEAR(NOW()) > DAYOFYEAR(birthday),
CONCAT( YEAR(NOW())+1, DATE_FORMAT(birthday, '%j') ),
CONCAT( YEAR(NOW()), DATE_FORMAT(birthday, '%j') )
) as _birthdaySortField",
'limit' => '3',
'orderBy' => '_birthdaySortField',
));
?>

Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Displaying Birthdays : How to show Entries with only Current / Future Dates?

By Dave - February 17, 2010 - edited: February 17, 2010

Actually, this one is even simpler.

<?php

require_once "/home/kzoopeak/public_html/cmsAdmin/lib/viewer_functions.php";

list($member_directoryRecords, $member_directoryMetaData) = getRecords(array(
'tableName' => 'member_directory',
'limit' => '3',
'orderBy' => "DAYOFYEAR(NOW()) > DAYOFYEAR(birthday), DAYOFYEAR(birthday)",
));
?>

It sorts by whether the birthday has passed for the current year, then by the day of year of the birthday.
Dave Edis - Senior Developer
interactivetools.com