[SOLVED] Is there a simpler way to do these queries?

[AStrangerWCandy]

So I have a site with a private member system. When users register they indicate their country. This is stored in it's own column in a mySQL database and is recalled later to display a little flag on their user profiles.

 

I'd like to create a site stats page that shows the # of users from any particular country. There are 200+ countries listed as options to choose from. Is there a way to create a page that says the count for each country type without doing 200+ count SQL queries? (Meaning could I do one query and then sort through that data after having done the query)

[Mark Baker]

Yes

SELECT countryName, COUNT(userID) as users
  FROM userTable
GROUP BY countryName

[AStrangerWCandy]

Sorry for my newbishness but how would that translate over to making a line like

 

<tr><td><img src="$countryname.jpg"> $CountryName</td><td> $number_of_users Members</td></tr>

[Mark Baker]

Assuming you're using MySQL:

$query = "SELECT countryName, COUNT(userID) as users
  FROM userTable
GROUP BY countryName";

$result = mysql_query($query);

echo '<table>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>><td><img src="'.$row['countryName'].'.jpg"> '.$row['countryName'].'</td>';
    echo '<td>'.$row['users'].' Members</td></tr>';
}
echo '</table>';

[AStrangerWCandy]

You are awesome ty for showing me how to do that!

[cunoodle2]

Please make this topic as "solved" so that people know you don't need any more help.