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>
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,
Technical Lead
interactivetools.com
By gkornbluth - May 3, 2019
Wow, was that easy.
Never knew about groupBy.
Pretty handy...
Just added it to the existing page and it worked like I really knew what I was doing.
Thanks Daniel, have a great weekend...
Jerry
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php