Group By date

5 posts by 3 authors in: Forums > CMS Builder
Last Post: July 30, 2012   (RSS)

By terryally - December 19, 2011

Hi,

I have a listing of shows which may spread over 12 or more months.

I currently have them grouped by month but I need them grouped by month AND year.

The current listing is by calendar order but I want a chronological order i.e.

Rather than:

Jan (2012)
Feb (2012)
Mar (2012)
... continuing to...
Nov (2012)
Dec (2011)

I would like:

Dec (2011)
Jan (2012)
Feb (2012)
Mar (2012)
Nov (2012)
Dec (2012)

Is there a way to get this done?

The code I currently use to create each month's listing is:

<!-- ********* JANUARY ********* -->
<?php
list($showsRecords, $showsMetaData) = getRecords(array(
'tableName' => 'shows',
'where' => 'start_date >= NOW() AND MONTH(start_date) = 1',
'orderBy' => 'start_date ASC',
));
$showsRecord = @$showsRecords[0]; // get first record
?>


<!-- ********* FEBRUARY ********* -->
<?php
list($showsRecords, $showsMetaData) = getRecords(array(
'tableName' => 'shows',
'where' => 'start_date >= NOW() AND MONTH(start_date) = 2',
'orderBy' => 'start_date ASC',
));
$showsRecord = @$showsRecords[0]; // get first record
?>

etc etc


Thanks
Terry

Re: [terryally] Group By date

By Jason - December 19, 2011

Hi Terry,

Try this, you may find this approach easier.

Instead of having a separate query for each month, select all the records in one query, then organize them by month/year like this:

list($showsRecords, $showsMetaData) = getRecords(array(
'tableName' => 'shows',
'where' => 'start_date >= NOW()',
'orderBy' => 'start_date ASC',
));

$showsByDate = array();

foreach ($showsRecords as $show) {
$dateHeader = date("M (Y)", strtotime($show['start_date']));

if (!array_key_exists($header, $showsByDate)) {
$showsByDate[$dateHeader] = array();
}

$showsByDate[$dateHeader][] = $show;
}

At the end of this code, the variable $showsByDate will have all of your show records, organized by a month/year header.

You can then output this by using two foreach loops like this:

<?php foreach ($showsByDate as $header => $shows): ?>
<?php echo $header; ?>

<?php foreach ($shows as $show): ?>
// output show information here
<?php endforeach ?>
<?php endforeach ?>

Hope this helps get you started.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Group By date

By terryally - December 23, 2011

Hello Jason,

This lists them exactly how I wanted.

Thank you very much.

Best wishes for Christmas and 2012 to you, Dave and all other colleagues at InteractiveTools.com.

Terry

Re: [thenetgirl] Group By date

By Jason - July 30, 2012

Hi,

The variable $show is specific to the example in the previous post. In the code you provided, it would cause an "undefined variable" error.

Also, you probably won't want to use a date as a variable to group by, and would probably want to use your "vendor" field in stead.

Hope this helps get you started.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/