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 ?>

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

Actually, by some miracle, I managed to get this working from a sort of related example here. I thought this would be more difficult and using arrays. I don't know how it holds up if I was to put a 2 year schedule or something (probably won't ever happen). Look ok? If this is an old way and you would use arrays or some other CMSB function I don't know about, feel free to show me a more powerful way. Thanks.

<?php $monthCheck = ""; ?>
<?php foreach ($dancer_scheduleRecords as $record):
$showMonth = false;
$theMonth = date("F", strtotime($record['show_date']));
if ($theMonth != $monthCheck) :
$monthCheck = $theMonth;
$showMonth = true;
endif;
?>
	<?php if ($showMonth): ?>
		<strong><?php echo $theMonth; ?></strong><br />
	<?php endif ?>
	<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 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