[SOLVED] add number of rows to province, city and suburb

[jmcc]

Hi

I have a property site, and I would like to know how do I add the total amount of properties listed in each province, city an suburb?

 

I have a simple search engine which works on a dropdown.

 

how do i get that amount to show next to each province, city and suburb?

 

Example:

province1(22)

province2(55)

 

City1(1)

City2(87)

 

suburb1(35)

suburb2(34)

[kickstart]

Hi

 

You would need to calculate it.

 

If you had a crude single table of  (say):-

 

PropertyTable

Id

PropertyDetails

province

City

suburb

 

You would do something like:-

 

SELECT province, COUNT(*)

FROM PropertyTable

GROUP BY province

 

Do the same for City and suburb.

 

If you had a more complex system with, say a table of properties, with a column for suburb. Say that you then have another table of suburbs listing the city they are in and then a further take of cities listing the province they are in, with lastly a table of provinceds

 

PropertyTable

Id

PropertyDetails

suburbId

 

SuburbTable

Id

SuburbName

CityId

 

CityTable

Id

CityName

ProvinceId

 

ProvinceTable

Id

ProvinceName

 

You would then use these 3 to get the 3 drop downs.

 

SELECT SuburbName, COUNT(*)

FROM PropertyTable

INNER JOIN SuburbTable

ON PropertyTable.suburbId = SuburbTable.id

GROUP BY SuburbName

 

SELECT CityName, COUNT(*)

FROM PropertyTable

INNER JOIN SuburbTable

ON PropertyTable.suburbId = SuburbTable.id

INNER JOIN CityTable

ON SuburbTable.cityId = CityTable.id

GROUP BY CityName

 

SELECT ProvinceName, COUNT(*)

FROM PropertyTable

INNER JOIN SuburbTable

ON PropertyTable.suburbId = SuburbTable.id

INNER JOIN CityTable

ON SuburbTable.cityId = CityTable.id

INNER JOIN ProvinceTable

ON CityTable.ProvinceId = ProvinceTable.id

GROUP BY ProvinceName

 

All the best

 

Keith

[jmcc]

Thank you this is really helpful.

The numbers seems to be wrong.

 

This is what I have tried:

 

---province---

SELECT province, COUNT(*)

FROM seller

INNER JOIN suburb

ON seller.suburb = suburb.name_suburb

INNER JOIN city

ON suburb.city_suburb = city.name_city

INNER JOIN province

ON city.prov_city = province.name_prov

GROUP BY province

---End---

 

 

---city---

SELECT city, COUNT(*)

FROM seller

INNER JOIN suburb

ON seller.suburb = suburb.name_suburb

INNER JOIN city

ON suburb.city_suburb = city.name_city

GROUP BY city

---End---

 

---suburb---

SELECT suburb, COUNT(*)

FROM seller

INNER JOIN suburb

ON seller.suburb = suburb.name_suburb

GROUP BY suburb

--end---

[jmcc]

When I run the query on my query browser it works but on the website my dropdowns are empty

 

Ok this is what i'v tried:

 

mysql_select_db($database_prop, $prop);

$query_province = "SELECT province, COUNT(*) FROM seller GROUP BY province";

$province = mysql_query($query_province, $prop) or die(mysql_error());

$row_province = mysql_fetch_assoc($province);

$totalRows_province = mysql_num_rows($province);

 

mysql_select_db($database_prop, $prop);

$query_city = "SELECT city, COUNT(*) FROM seller GROUP BY city";

$city = mysql_query($query_city, $prop) or die(mysql_error());

$row_city = mysql_fetch_assoc($city);

$totalRows_city = mysql_num_rows($city);

 

mysql_select_db($database_prop, $prop);

$query_suburb = "SELECT suburb, COUNT(*) FROM seller GROUP BY suburb";

$suburb = mysql_query($query_suburb, $prop) or die(mysql_error());

$row_suburb = mysql_fetch_assoc($suburb);

$totalRows_suburb = mysql_num_rows($suburb);

[kickstart]

Hi

 

Not quite sure what you are trying to get with:-

 

$query_province = "SELECT province, COUNT(*) FROM seller GROUP BY province";

 

That looks like it should get you a list of provinces and a count of sellers for each province.

 

However you do not appear to check either of the values returned, just to grab a count of the total rows returned (so basically a count of provinces).

 

How are you processing the returned data?

 

All the best

 

Keith

[jmcc]

Hi

 

thanks for your help I was able to get the correct count.