Field from url

If all you want is a list of the cities and how many there are in each city, then you should be able to use the groupBy function as in the example below, and modify the code to suit your needs.

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

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

<!-- Insert Active Code Here -->

<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 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; ?>
</div>
</div>
</div>

If you're looking for the visitor to do a search to determine the city in question, you can take a look at some of the recipes, like http://www.thecmsbcookbook.com/recipedetail.php?Allowing-Visitor-To-Set-Where-values-444 or the "Search" recipes in my CMSB Cookbook (free trial link below) to search for a city by name and then use the resulting records to give a record count:

<?php $count = "" // reset Counter ?>
<?php foreach ($your_sectionRecords as $record): ?>
<?php $count++ // increment the counter by +1 ?>
<?php endforeach?>

And then echo that count along with the city name searched for

Hope that helps

Jerry Kornbluth