I have a table of postcode prefixes called ‘postalAreas’ as follows:
id | tinyint(250)
areaCode | varchar(3)
I also have another table called ‘agentToPostcode’ as follows:
areaCode | varchar(3)
agentId | tinyint (250)
The first table has a full list of 124 area codes.
When an agent is added to the database, the “agentToPostcode” table is updated with the area code for this agent.
So what I want to do is display a list of all 124 area codes and next to each to display how many of each agent there are assigned to it.
I suspect the following code would do this however it seems really inefficient and I would like to know if there is a solution to do this with just one query?
$query="SELECT * FROM postalAreas ORDER BY areaCode";
$result = @mysql_query ($query); // Run the query
while ($r = mysql_fetch_array($result)) {
$areaCode=$r["areaCode"];
$query2="SELECT * FROM agentToPostcode WHERE areaCode = $areaCode";
$result2 = @mysql_query ($query2);
$num_rows = mysql_num_rows($result2);
echo "Area Code " . $areaCode . " Total: " . $num_rows;
}