[SOLVED] How to count the # if instances an ID is in a table?

[BadGoat]

Hi!

 

I am trying to count the # of instances where an ID appears in a table. I have a city table where each city has an id and a region id, a region table where the region has an id. I want to be able to count the # of cities are in a region by id. Everything I've tried is either 0 or 4, no clue how I got 4, even though I verified that none of the regions had only 4 cities.

 

Here's a  bit of code, where I'd been trying to get it to work.

<tr>
    <td>'.$row['region'].' contains ';
    $query1 = "SELECT SUM(region_id) AS totalcons FROM e_city WHERE  region_id = '$region_id'";
    $result = mysql_query($query1);
    $r = mysql_fetch_array($result);
echo number_format($totalcons);
echo' Cities.</td>
</tr>

 

Please point me in the right direction. Thanks!

[lemmin]

If you just count the results that have that region id, wouldn't that be correct?

"SELECT count(cityid) as citycount FROM cities WHERE regionid = 1";

[BadGoat]

OK, I see that COUNT is the right choice. Now when I want to echo the count, is it this?

 

echo count($citycount);

 

[supanoob]

no i think it would be

 

echo "$count";

[sasa]

echo $r['citycount'];

[BadGoat]

sasa, you rock!