Solved

Need help with a query using Distinct

Posted on 2008-06-11
3
191 Views
Last Modified: 2008-06-11
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
Comment
Question by:lvollmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
Rob Siklos earned 500 total points
ID: 21762618

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

Author Comment

by:lvollmer
ID: 21762692
PERFECT! THANKS - if I want to order by most records first, how do I amend the query?
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21762732
Try this:
$sql = "SELECT City, COUNT(*) AS cnt
            FROM venue 
            GROUP BY City
            ORDER BY cnt";

Open in new window

0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question