USING GROUPBY TO CREATE A LIST OF RECORDS THAT HAVE THE SAME VALUES AND ONLY RETURN ONE RECORD PER GROUP - May 28th, 2019


I had created a service provider directory for a client who wanted to create a list of the countries and
states/provinces that had provider listing records (accounts). They also wanted to show a count of how many listings
there were for each country and each state/province, eliminating any hidden listings.

With a great deal of help from Daniel Loewe and Greg Thomas, here’s what we came up with.

You can see the result at https://dbtproviders.com/maplist.php

Daniel first suggested using the groupBy function to create a list of records that have the same values and only return
one record per group.


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


Then to display the list, he suggested the following (Greg added the code for counting):


<?php $old_group = ''; // init blank var.

foreach ($accountsRecords as $record): ?>
<?php if (!$record['practice_country:label'] == '' ):?>
<?php $group = $record['practice_country:label']; // load sub-group value from record. ?>
<?php else : ?>
<?php $group = $record['other_practice_country']; ?>
<?php endif ?>
<?php $stateCount = mysql_count('accounts', mysql_escapef("`practice_state` = ? AND `hidden` = '0'
",$record['practice_state'])); ?>
<?php $countryCount = mysql_count('accounts', mysql_escapef("`practice_country` = ? AND `hidden` = '0'
",$record['practice_country'])); ?>
<div class="rTableRow">
<div class="rTableCell text_font">
<?PHP
if ($group !== $old_group) {echo "<h3>$group ($countryCount Listings)</h3>";} ?>
<?php $state = ($record['practice_state']); ?>
<?php $country = ($record['practice_country:label']); ?>
<?php $recnum = ($record['num']); ?>
<a href="maps.php?state=<?php echo $state ?>&country=<?php echo $country ?>"><?php echo ucwords($state);
?> (<?php echo $stateCount ?>) </a></div>
</div>
<?PHP $old_group = $group; // retain sub-group name before moving to new record. ?>
<?php endforeach; ?>




The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.


Terms of Service