List by month

5 posts by 3 authors in: Forums > CMS Builder
Last Post: November 11, 2021   (RSS)

By rez - October 28, 2021 - edited: October 28, 2021

I have 2 editors.

editor 1: dancer_schedule - show_date, dancers (checkbox list from editor 2)

editor 2: dancers - name, photo, content

In editor 1:

Show Date: calendar picker, you select month, day, year

Dancer Selection: checkbox list of 5 dancers,  coming from editor 2

Announcement: just a text field for any notes

I want the viewer to display on the page like this:

NOVEMBER

Thu, Nov 4th: Jen, Haley
Fri, Nov 5th: Haley, Jessica
Sat, Nov 6th: Jessica
Thu, Nov 11th: Rachel, Jen

DECEMBER

Thu, Dec 2nd: Rachel, Jen
Fri, Dec 3rd: Haley
Sat, Dec 4th: Haley
Thu, Dec 9th: Jen, Jessica
Fri, Dec 10th: Jen

I can get a simple list to display easily but I don't know how to get them to display by month, under a month title like above.

list($dancer_scheduleRecords, $dancer_scheduleMetaData) = getRecords(array(
'tableName' => 'dancer_schedule',
'loadUploads' => true,
'allowSearch' => false,
'where' => " show_date >= CURDATE() ",
));


list($dancersRecords, $dancersMetaData) = getRecords(array(
'tableName' => 'dancers',
'loadUploads' => true,
'allowSearch' => false,
));



<?php foreach ($dancer_scheduleRecords as $record): ?>

<strong><?php echo date("D, M jS", strtotime($record['show_date'])) ?>: </strong>
<?php echo join(', ', $record['dancers:labels']); ?><br>

<?php endforeach ?>

Hi Rez,

Do a google search for MySQL group records by month and look into the Group By function

You may find some interesting ideas

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By rez - October 30, 2021

Oh, thanks. I'll look into it.

By daniel - November 11, 2021

Hey rez,

This method looks fine! Though as you note, it would have issues if you were to enter dates across multiple years. When generating $theMonth you could probably just use "F Y" instead of "F" to account for the year if that's ever necessary.

I'd also recommend adding an "orderBy" option to your getRecords() to sort by show_date - something like this:

list($dancer_scheduleRecords, $dancer_scheduleMetaData) = getRecords(array(
'tableName' => 'dancer_schedule',
'loadUploads' => true,
'allowSearch' => false,
'where' => " show_date >= CURDATE() ",
'orderBy' => "show_date ASC",
));

If you create the records in chronological order the default sorting will usually work, but it's not guaranteed, and this grouping method won't work correctly if any records are out of date order.

Let me know if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com