Counting records in GroupBy categories

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

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>

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

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

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