Jump to content

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


gabrielkolbe

Recommended Posts

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>

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>

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>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.