Code to Pull # of Pilots in Hub

[StartVM]

Does anyone have the code to pull a numerical value of the number of pilots in a specific hub? Also, the amount of flights done by pilots in that hub? Thanks!

[Tom]

The first, yup https://github.com/tomsterritt/HubStats

It doesn't do the number of flights done by just those pilots, but does have the number of flights in/out the hub. You can probably quite easily add that though

[freshJet]

You can do #2 by:

public static function FlightsByPilotByHub($pilotid, $icao) {
$query = "SELECT COUNT(id) as flights FROM phpvms_pireps WHERE depicao = '$icao' OR arricao = '$icao' AND pilotid = '$pilotid' AND accepted = '1'";
$result = DB::get_row($query);
return $result->flights;
}

Not tested.

[Tom]

You can do #2 by:

public static function FlightsByPilotByHub($pilotid, $icao) {
$query = "SELECT COUNT(id) as flights FROM phpvms_pireps WHERE depicao = '$icao' OR arricao = '$icao' AND pilotid = '$pilotid' AND accepted = '1'";
$result = DB::get_row($query);
return $result->flights;
}

Not tested.

That would get the flights by a specific pilot

To get flights for all pilots at a hub it would be something like:

public static function CountFlightsForPilotsOfHub($icao) { //Number of flights just for pilots at this hub
$query = "SELECT COUNT(pirepid) as count FROM ".TABLE_PREFIX."pireps WHERE (arricao = '".$icao."' OR depicao = '".$icao."') AND pilotid IN(SELECT pilotid FROM ".TABLE_PREFIX."pilots WHERE hub = '".$icao."')";
$results = DB::get_row($query);
return $results->count;
}

You could probably make it more efficient using a JOIN instead but I don't have time to work it out right now

[Strider]

https://github.com/Strider2/phpvms_hub

That uses Tom's hubstats stats class.

[freshJet]

That would get the flights by a specific pilot

To get flights for all pilots at a hub it would be something like:

public static function CountFlightsForPilotsOfHub($icao) { //Number of flights just for pilots at this hub
$query = "SELECT COUNT(pirepid) as count FROM ".TABLE_PREFIX."pireps WHERE (arricao = '".$icao."' OR depicao = '".$icao."') AND pilotid IN(SELECT pilotid FROM ".TABLE_PREFIX."pilots WHERE hub = '".$icao."')";
$results = DB::get_row($query);
return $results->count;
}

You could probably make it more efficient using a JOIN instead but I don't have time to work it out right now

Isn't that what he wants? Per pilot from the hub?

[Tom]

Isn't that what he wants? Per pilot from the hub?

Does anyone have the code to pull a numerical value of the number of pilots in a specific hub? Also, the amount of flights done by pilots in that hub? Thanks!

My understanding of "flights done by pilots in that hub" would be all of them..

Either way both understandings have now been answered for.