Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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.
0
lvollmer
Asked:
lvollmer
  • 2
1 Solution
 
Rob SiklosCommented:

<?
$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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now