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: [Jason] Group By date

By thenetgirl - July 27, 2012

I have been looking at this code and trying to figure out this code ..its all the shows[/#990000] I am not getting.



// load records
list($auto_referralsRecords, $auto_referralsMetaData) = getRecords(array(
'tableName' => 'auto_referrals',
'orderBy' => 'createdDate DESC',
'where' => 'start_date >= NOW()',

$showsByDate = array();
foreach ($auto_referrals as $record) {
$dateHeader = date("M (Y)", strtotime($show['start_date'])); if (!array_key_exists($header, $showsByDate))
{

$showsByDate[$dateHeader] = array();
}
$showsByDate[$dateHeader][] = $show;
}
));


trying have only one header per Vender that posts a referral - and all thier clients under them in a table list...
for the client to see via the web.

right now I get a header and record each time
See https://www.collisionrecoverynetwork.com/images/chart1.jpg

this is my code without the table

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

<?php if ($record['createdByUserNum'] == $CURRENT_USER['num'] || $CURRENT_USER['isAdmin']): ?>
<?php echo $record['createdBy.business_name'] . " " . $record['createdBy.fullname'] . " " . $record['createdBy.city'] . " " .$record['createdBy.state'] ?>
<font face="Verdana" size="1"><?php echo $record['num'] ?><?php echo join(', ', getListLabels('auto_referrals', 'priority', $record['priority'])); ?><?php echo $record['name'] ?><p><?php echo $record['address'] ?>,
<br>
<?php echo $record['city'] ?>,<?php echo $record['state'] ?> <?php echo $record['zip'] ?></font></p>
<font face="Verdana" size="1"><?php echo $record['contact_phone'] ?><a href="mailto:<?php echo $record['email'] ; ?>"><?php echo $record['email'] ?></a><?php echo $record['date_of_loss'] ?></font><p>
<font face="Verdana" size="1">&nbsp;<?php echo $record['vehicle_make'] ?></font></p>
<font face="Verdana" size="1">&nbsp;<?php echo $record['vehicle_model'] ?></font><font face="Verdana" size="1"><?php echo $record['vehicle_year'] ?></font><b><font size="4" face="Arial Black"><p>

<?php if ($record['passed_to_legal'] == '1'): ?>
<font color="#FFCC00" face="Arial Black" size="4">*</font>
<?php endif ?>
<?php if ($record['passed_to_medical'] == '1'): ?>
</font><font color="#000080" face="Arial Black">*</font><font face="Arial Black"><font color="#000080">
</font>
<?php endif ?>

<?php if ($record['passed_to_total_loss'] == '1'): ?>
</font><font color="#CC0000" face="Arial Black">*</font><font size="4" face="Arial Black">
<?php endif ?>

<?php endif ?>

<?php endforeach ?>
Patricia

www.thenetgirl.com