Need help with a query using Distinct

I want to query a table in a database. The table is called venue. I want to grab all of the distinct city names in that table and do the following:

<div id="masterdiv">
      <div class="label">
            <strong>Location</strong></a>
      </div>
      
            <div class="sublabel">
<!-- THIS IS WHERE THE FIRST 5 RESULTS WOULD BE - I ALSO NEED TO ECHO THE NUMBER OF INSTANCES OF THAT CITY -->
            - <a href="#">MIDDLETOWN</a> [30 records]<br>
            - <a href="#">GARNERTOWN</a> [18 records]<br>
            - <a href="#">BTOWN</a> [15 records]<br>
            - <a href="#">NICETOWN</a> [6 records]<br>
            - <a href="#">BOOMTOWN</a> [12 records]<br>
            </div>
      <div class="menutitle" onclick="SwitchMenu('sub1')">Click for more</div>
      <span class="submenu" id="sub1">
<!-- THIS IS WHERE THE REST OF THE CITY NAMES SHOULD GO -->
            - <a href="#">NEWTOWN</a>[12 records]<br>
      </span>

</div>


Can anyone help me out? I am failing at every attempt.
lvollmerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob SiklosConnect With a Mentor Commented:

<?
$sql = "SELECT City, COUNT(*) AS cnt
            FROM venue 
            GROUP BY City";
 
$result = mysql_query($sql);
$rows = array();
while ($row = mysql_fetch_assoc($result)) {
   $rows[] = $row;
}
?>
 
<div id="masterdiv">
      <div class="label">
            <strong>Location</strong></a>
      </div>
      
            <div class="sublabel">
<!-- THIS IS WHERE THE FIRST 5 RESULTS WOULD BE - I ALSO NEED TO ECHO THE NUMBER OF INSTANCES OF THAT CITY -->
              <?
                for ($i = 0; $i < 5; $i++) {
                   $row = $rows[$i];
                   echo '- <a href="#">' . $row['City'] . '</a> [' . $row['cnt'] . ' record(s)]<br>';
                }
              ?>
            </div>
      <div class="menutitle" onclick="SwitchMenu('sub1')">Click for more</div>
      <span class="submenu" id="sub1">
<!-- THIS IS WHERE THE REST OF THE CITY NAMES SHOULD GO -->
              <?
                for ($i = 5; $i < count($rows); $i++) {
                   $row = $rows[$i];
                   echo '- <a href="#">' . $row['City'] . '</a> [' . $row['cnt'] . ' record(s)]<br>';
                }
              ?>
      </span>
 
</div>

Open in new window

0
 
lvollmerAuthor Commented:
PERFECT! THANKS - if I want to order by most records first, how do I amend the query?
0
 
Rob SiklosCommented:
Try this:
$sql = "SELECT City, COUNT(*) AS cnt
            FROM venue 
            GROUP BY City
            ORDER BY cnt";

Open in new window

0
All Courses

From novice to tech pro — start learning today.