Counting records in GroupBy categories

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

By gkornbluth - May 23, 2019

Hi all,

In post 2243344 Daniel Louwe helped me to display listing categories grouped by country and state. https://dbtproviders.com/maplist.php

I've been trying to show the number of records in each country and state. I'm having no luck understanding the syntax required anything that I've found searching for groupBy and Count and would appreciate any help achieving my goal.

Here's the current code (without the 2 counts).

At the top of the page:

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

And in the body:

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

foreach ($accountsRecords as $record):
$group = $record['practice_country:label']; // load sub-group value from record. ?>

<div class="rTableRow">
<div class="rTableCell text_font"><?PHP
if ($group !== $old_group) {echo "<h3>$group <?php // (count of records in $group) ?></h3>";} ?>
<?php $state = ($record['practice_state']); ?>
<?php $country = ($record['practice_country:label']); ?>
<a href="maps.php?state=<?php echo $state ?>&country=<?php echo $country ?>"><?php echo ucwords($state); ?> <?php // (count of records in each $state) ?> </a></div>

</div>
<?PHP $old_group = $group; // retain sub-group name before moving to new record. ?>
<?php endforeach; ?>

Thanks,

Jerry Kornbluth

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 gkornbluth - May 24, 2019 - edited: May 24, 2019

Hi Greg,

Haven't heard from you in a while.

Thanks for the insights, It worked perfectly with a few minor tweaks.

Best,

Jerry Kornbluth

Just in case it can help someone else, here's my final code.

At the top of the page:

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

And in the Body:

<div align="center" style="width:80%; text-align:left"><span class="heading_font"><b>PROVIDER MAPS</b><br />
<br />
</span> <span class="text_font">We have provider listings in the following Countries/States/Provinces<br />
Choose the one that's right for you</span><br />
<br />
<div class="rTable">
<div class="rTableBody">
<?php $old_group = ''; // init blank var.

foreach ($accountsRecords as $record): ?>
<?php $group = $record['practice_country:label']; // load sub-group value from record. ?>
<?php $stateCount = mysql_count('accounts', mysql_escapef("`practice_state` = ? AND `hidden`= '0' ",$record['practice_state']));  // revised to not include hidden records ?>
<?php $countryCount = mysql_count('accounts', mysql_escapef("`practice_country` = ? AND `hidden`= '0' ",$record['practice_country'])); // revised to not include hidden records ?>
<div class="rTableRow">
<div class="rTableCell text_font">
<?php if ($group !== $old_group) {echo "<h3>$group ($countryCount)</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; ?>
</div>
</div>

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 gkornbluth - May 24, 2019 - edited: May 24, 2019

Hi Greg,

Oops, I spoke too soon.

Seems that the code you suggested counts all records, hidden or not.

Any way I can hide the hidden records from the counts?

https://dbtproviders.com/maplist.php

Thanks,

Jerry Kornbluth

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 gregThomas - May 24, 2019

Hey Jerry,

You just need to update the where statements in the MySQL count functions so that it excludes the hidden records:

  $stateCount    = mysql_count('accounts', mysql_escapef("`practice_state` = ? AND `hidden` = '0' ",$account['practice_state']));
  $countryCount  = mysql_count('accounts', mysql_escapef("`practice_country` = ?  AND `hidden` = '0' ",$account['practice_country']));

You can add any other where statements you need here as well. 

Cheers!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By gkornbluth - May 24, 2019 - edited: May 24, 2019

Great, thanks Greg.

I didn't know that.

I've updated the example code and created a recipe in the CMSB Cookbook as well.

Have a great weekend.

Jerry Kornbluth

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