Listing field values only once when they appear in a grouped list

3 posts by 2 authors in: Forums > CMS Builder
Last Post: May 3, 2019   (RSS)

By gkornbluth - May 2, 2019

Hi all,

Sorry, but I'm back again...

This time I’m trying to create a list of the states/provinces in the 'practice_state' field in an accounts database, grouped by the country they're in ('practice_country' is a list field in the accounts database, which gets its values from a master list of countries).

I’ve attempted to use some code that I’ve used before to create groups of categories and questions, and I’m almost there, but no matter what I’ve tried, I’m still stuck in one area.

I can't have any of the existing states/provinces listed on the list page more than once.

You can see the issue at http://www.dbtproviders.com/maps.php (current existing states (CA, CT, MD, NY) should only be listed once each, not for each record found).

Hope someone can help solve the riddle...

Thanks,

Jerry Kornbluth

Here’s the code I’m using:

list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'orderBy' => 'practice_country ASC , practice_state ASC ',

));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<table>
<?php $old_group = ''; // init blank var.
foreach ($accountsRecords as $record):
$group = $record['practice_country:label']; // load sub-group value from record. ?>
<tr>
<td align="left" ><?PHP
if ($group !== $old_group) {echo "<h2>$group</h2>";} ?>
<a href="maps.php?state=<?php echo $record['practice_state'] ?>">state=<?php echo ucwords($record['practice_state']); ?></a>
</td>
</tr>
<?PHP $old_group = $group; // retain sub-group name before moving to new record. ?>
<?php endforeach; ?>
</table>
</body>
</html>

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 - May 3, 2019

Hi Jerry,

It looks like you already have part of a solution with you're handling the Country. This could be expanded to also handle state/province (duplicate the variables and add another if() condition), though it gets somewhat messy when trying to handle two sets of values like this.

An alternate idea that I think could work is adding a 'groupBy' to your getRecords, like this:

list($accountsRecords, $accountsMetaData) = getRecords(array(
  'tableName' => 'accounts',
  'orderBy' => 'practice_country ASC , practice_state ASC ',
  'groupBy' => 'practice_country, practice_state',
));

This will tell effectively tell the query to "group" records that have the same values for both 'practice_country' and 'practice_state'. Grouping has a few different uses, but in this case, the important thing is that it should only return one record per group, meaning only one record per country/state pair.

Try this out and let me know how it goes!

Thanks,

Daniel
Technical Lead
interactivetools.com