[SOLVED] display cities and count how many properties per city

[gabrielkolbe]

Hi, I have a property website, on it I have a drop down box where all cities are displayed. I want to count how many properties there are for each city and then display the number next to the city, so that users can know how many properties are available for each city.

 

Here is my code so far, I would appreciate someone to help me here!!

Don't be confused by the code I have tried various ways of making this work.

NOTE: I think it would probably be easier to push the cities in an array, use the unique array function to only display 1 of each city, BUT before this, count the amount of times each city appears....

 

 <select name="city" class="formfields">
    <option value="All" selected>All Cities</option>
    <?
//$i = 0;
$cities = array();
$query = "SELECT * FROM prop order by city";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
array_push($cities, $row['city']);
//for ($i = 1; $i <= 10; $i++) {
//    echo $i;
//}

//if (array_key_exists($row['city'], $cities)) {
//$i=$i+1;


  echo '<option>' . $row['city'] .' [' . $i . ']</option>';
  }
}


?>
  </select>

[bbaker]

how's 'bout this?

 

<select name="city" class="formfields">
<option value="All" selected>All Cities</option>
<?php
$query = "SELECT * FROM prop GROUP BY city ORDER BY city"; //group by city to only show city once.
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
  echo '<option>' . $row['city']; //echo city
  $query2 = mysql_query("SELECT COUNT(city) AS num FROM prop WHERE city = '".$row['city']."'"); //query the number for each city
  $row2 = mysql_fetch_array($query2);
  echo ' ['.$row2['num'].']'; // echo the number
  echo '</option>';
}
?>

</select>

[gabrielkolbe]

thank you

[bbaker]

actually, an easier way would be to do this:

<select name="city" class="formfields">
<option value="All" selected>All Cities</option>
<?php
$query = "SELECT city,COUNT(city) AS num FROM prop GROUP BY city ORDER BY city";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
  echo '<option>' . $row['city'].' ['.$row['num'].']</option>';
}
?>
</select>