Total Pilots From Country?

[Thomas]

Hey Guys,

Well this is a record,  I aint asked for help for a bit of time!

Ok, So is there a way to do something like this...

(UK FLAG) Total Pilots: 100

(US FLAG) Total Pilots: 100

(DE FLAG) Total Pilots: 100

etc...

Thomas.

[Nabeel]

$params = array(
'table' => TABLE_PREFIX.'pilots',
'fields' => 'location, COUNT(*) as `total`',
'group' => 'location ORDER BY total DESC',
);

$results = DB::quick_select($params);
if(!$results)
{
return 0;
}

That should return a list with all the countries and totals, looking like this:

Array
(
[0] => stdClass Object
(
[location] => 
[total] => 1
)

[1] => stdClass Object
(
[location] => AF
[total] => 6
)

[2] => stdClass Object
(
[location] => AT
[total] => 1
)

[3] => stdClass Object
(
[location] => AU
[total] => 2
)
... etc

This will rearrange them to be accessed per-country

$country = array();
foreach($results as $row)
{
$country[$row->location] = $row->total;
}

echo "pilots in the US: {$country['US']}";

Which will output:

pilots in the US: 6

[Guest chikolol]

wow thank you for that! very good snippet here!

[Thomas]

<?php 
$params = array(
       'table' => TABLE_PREFIX.'pilots',
       'fields' => 'location, COUNT(*) as `total`',
       'group' => 'location',
);

$results = DB::quick_select($params);
if(!$results)
{
       return 0;
}
?>

Would that be the correct code?

-Thomas.

[Nabeel]

Yep. I moved this topic to code snippets and appended my post above

[Thomas]

Ok Cool Nabeel, Thanks.

One Other thing, Is there anyway to put the flag there instead of the US text?

Thomas.

[Nabeel]

Yep, just do a search on that.

http://forum.phpvms.net/topic/809-pilot-country-flags/page__p__4989__hl__getCountryImage__fromsearch__1entry4989

[mark1million]

Nabeel,

I want to develop this further to be able to display something like this,

[Nabeel]

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.

[mark1million]

OK so i have this working in to a table OK, is there any way to display the name, ie United States next to the country flag?

[Nabeel]

I think its

Countries::getCountryName($country->location)

[mark1million]

I think its

Countries::getCountryName($country->location)

Yes that's the code in the pilot_public_profile.tpl, but its not working in the code for some reason.

This is what im trying,

<h3>Pilot Locations</h3>
<?php 
$sql = " SELECT COUNT(pilotid) as total, location FROM phpvms_pilots GROUP BY location LIMIT 0, 9999999 ";
echo '<table cellspacing="1" cellpadding="0" border="1">';
echo '<th width="100px"><div align="left">Country Flag</div></th>';
echo '<th width="100px"><div align="center">Pilots</div></th>';
$country_info = DB::get_results('SELECT COUNT(pilotid) as total, location FROM '.TABLE_PREFIX.'pilots GROUP BY location');
foreach($country_info as $country)
{
echo '<tr>';
echo '<td align= "center">';
echo '<img src="'.Countries::getCountryImage($country->location).'" /> ';
echo 'Countries::getCountryName($userinfo->location) ';
echo '</td>';
echo '<td align="center">';
echo ' ('.$country->total.')';
echo '</td>';
echo '</tr>';
}
echo '</table>';
?>

Its outputting the flag image OK but the location name is giving me, location) ?>

Any help would be appreciated,

Cheers.

[Nabeel]

Don't put this in quotes:

echo 'Countries::getCountryName($userinfo->location) ';

It should be:

echo Countries::getCountryName($country->location);

[mark1million]

Ha, you are the Jedi knight

Thanks thats got it.

[Nabeel]

No prob. BTW, you don't need this line:

$sql = " SELECT COUNT(pilotid) as total, location FROM phpvms_pilots GROUP BY location LIMIT 0, 9999999 ";

[Strider]

Has this been finished if so could you post the working code?

Cheers

Dan C

[mark1million]

Sure here you go. That will be £10

Of course only jocking :lol:

<table width="700px" border="0" cellspacing="0" cellpadding="1">
<?php
echo '<td width="250px" valign="top">'; 
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>';
$country_info = DB::get_results('SELECT COUNT(pilotid) as total, location FROM '.TABLE_PREFIX.'pilots GROUP BY location');
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>';
?>

You may need to mess about with the style and table but the function is there.