Solved

Need help with a query using Distinct

Posted on 2008-06-11
3
188 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
designing in object programming 12 79
paypal ipn to mysql 3 39
Insert values are dynamic 11 42
google analytics on wp-admin password protected area 2 24
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

809 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