What I am trying to do is display information from multiple tables in my database.
Refer to http://www.urbancanada.com/cities.php
What I want to do is display The Province name (in the "Province" table) and its population, which I have already done.
Now what I want to do is count the number of:
a.) cities are listed for a certain province (In another table named "city") and...
b.) The number of buildings, in each province (buildings is another seperate table)
here is my code, I have absolutly no idea how to do this, and if someone could modify my code to do what I need it to it would be very very very helpful.
<?php
$db = mysql_connect("localhost","dbinfo...","password");
mysql_select_db (dbinfo...);
$sql = "SELECT * FROM province order by population_rank asc";
if (!$result = mysql_query($sql)) {
print "Invalid query ($sql) : " . mysql_error();
exit;
}
while ($row = mysql_fetch_assoc($result)) {
$provincename = $row[name];
$id = $row[id];
$population = $row[population];
print "<table border=0 width=100% cellspacing=0 cellpadding=0>
<tr>
<td width=25%> <a href=urbanca.php?page=province&id=$id><B>$provincename</B></a></td>
<td width=25%> $population</B></a></td>
<td width=25%> $City_Count</B></a></td>
<td width=25%> $Building_Count</B></a></td></tr>
</table>";
}
?>
As you can see I dont know how to count the # of rows for buildings/ cities which are located in a diff table.
