Solved

Need help with a query using Distinct

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
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.

920 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

16 Experts available now in Live!

Get 1:1 Help Now