Stats: Sort Pilots per Country

[Nout - Zon Executive]

Hey Guys,

Anyway to get a small table that shows how many pilots from each country?

For example:

Germany -------- 23

USA ------------21

Spain -----------11

[Guest KaliKeira]

<h4>Pilot Country</h4>
<?php 
$country_info = DB::get_results('SELECT COUNT(pilotid) as total, location FROM '.TABLE_PREFIX.'pilots GROUP BY LOCATION'); 
$country_count = DB::get_row('SELECT COUNT(DISTINCT location) AS tot FROM '.TABLE_PREFIX.'pilots'); 
$column = '0'; 
if($country_count->tot >= 2) 
       { 
           $column = '1'; 
       } 
   if($column == '1') 
   { 
       echo '<table cellspacing="1" cellpadding="1" border="1">'; 
       echo '<tr><th width="150px"><div align="left">Country Location</div></th>'; 
       echo '<th width="100px"><div align="center">Pilots</div></th>'; 
       echo '<th width="150px"><div align="left">Country Location</div></th>'; 
       echo '<th width="100px"><div align="center">Pilots</div></th></tr>'; 
       foreach($country_info as $country) 
       { 
                if ($column % 2) 
                { 
                   echo '<tr>'; 
                } 
                   echo '<td align= "left">'; 
                   echo '<img src="'.Countries::getCountryImage($country->location).'" /> '; 
                   echo Countries::getCountryName($country->location); 
                   echo '</td>'; 
                   echo '<td align="center">'; 
                   echo ' ('.$country->total.')'; 
                   echo '</td>'; 
                if (!$column % 2) 
                { 
                   echo '</tr>'; 
                } 
           $column++; 
       } 
       if (!$column % 2) 
           { 
               echo '<td> </td><td> </td></tr>'; 
           } 
       echo '</table>'; 
   } 
   else 
   { 
       echo '<table cellspacing="1" cellpadding="1" border="1">'; 
       echo '<th width="150px"><div align="left">Country Location</div></th>'; 
       echo '<th width="100px"><div align="center">Pilots</div></th>'; 
       foreach($country_info as $country) 
           { 
               echo '<tr>'; 
               echo '<td align= "left">'; 
               echo '<img src="'.Countries::getCountryImage($country->location).'" /> '; 
               echo Countries::getCountryName($country->location); 
               echo '</td>'; 
               echo '<td align="center">'; 
               echo ' ('.$country->total.')'; 
               echo '</td>'; 
               echo '</tr>'; 
           } 
       echo '</table>'; 
   } 
?>

[El Macara]

thank you, I installed in my VA, its working very good...

[CPC900]

What would I need to change to have just ONE column all the way down, instead of 2?

Sorry, in other words.....A table that does not have 4 columns to it, just 2.

I took out the second:

echo '<th width="150px"><div align="left">Country Location</div></th>';

echo '<th width="100px"><div align="center">pilots</div></th></tr>';

But not sure what to take out of the rest?! And why do I need that "else" statement in there?

As usual, it is something simple I am not grasping, I am sure