Solved

Need help with a query using Distinct

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…
The viewer will learn how to count occurrences of each item in an array.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now