Group By date

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

Archived
Reply
Reply

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

Archived
Reply
Reply

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

Archived
Reply
Reply

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

Archived
Reply
Reply

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'] ?>
<?php echo $record['num'] ?><?php echo join(', ', getListLabels('auto_referrals', 'priority', $record['priority'])); ?><?php echo $record['name'] ?><?php echo $record['address'] ?>,
 
<?php echo $record['city'] ?>,<?php echo $record['state'] ?> <?php echo $record['zip'] ?>
<?php echo $record['contact_phone'] ?><a href="mailto:<?php echo $record['email'] ; ?>"><?php echo $record['email'] ?></a><?php echo $record['date_of_loss'] ?>
 <?php echo $record['vehicle_make'] ?>
 <?php echo $record['vehicle_model'] ?><?php echo $record['vehicle_year'] ?>

<?php if ($record['passed_to_legal'] == '1'): ?>
*
<?php endif ?>
<?php if ($record['passed_to_medical'] == '1'): ?>
*

<?php endif ?>

<?php if ($record['passed_to_total_loss'] == '1'): ?>
*
<?php endif ?>

<?php endif ?>

<?php endforeach ?>

Patricia

www.thenetgirl.com

Re: [thenetgirl] Group By date

Archived
Reply
Reply

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.