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

Hey Jerry,

I think you need to use the mysql_count function to count the total number of records for each state and country. Here is some example code:

  $state    = mysql_count('accounts', mysql_escapef("`practice_state` = ? ",$account['practice_state']));
  $country  = mysql_count('accounts', mysql_escapef("`practice_country` = ? ",$account['practice_country']));

This is example code, so you might need to make a few changes to get it working. So each mysql_count function will count the total number of records that have the same practice country and practice state as the current record.

You should be able to copy the code above and replace it with the $state and $country variables in your current code.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

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