I think the query for that will be something like:
SELECT COUNT(pilotid) as total, location FROM phpvms_pilots GROUP BY location
mysql> SELECT COUNT(pilotid) as total, location FROM phpvms_pilots GROUP BY location;
+-------+----------+
| total | location |
+-------+----------+
| 1 | |
| 7 | AF |
| 1 | AT |
| 2 | AU |
| 18 | BE |
| 1 | BF |
| 1 | BG |
| 2 | BR |
| 4 | CA |
| 1 | CV |
| 3 | DE |
| 1 | DK |
| 1 | ES |
| 3 | FR |
| 9 | GB |
| 1 | GR |
| 1 | HU |
| 2 | IE |
| 3 | IT |
| 2 | NL |
| 1 | NZ |
| 2 | PL |
| 3 | PT |
| 1 | RO |
| 1 | SE |
| 1 | SG |
| 7 | US |
| 1 | ZA |
+-------+----------+
28 rows in set (0.00 sec)
You can then loop through that list:
$country_info = DB::get_results('SELECT COUNT(pilotid) as total, location FROM '.TABLE_PREFIX.'pilots GROUP BY location');
foreach($country_info as $country)
{
echo '<img src="'.Countries::getCountryImage($country->location).'" /> ('.$country->total.')<br />';
}
That should give you the basics...you'll have to table-ize it and everything.