Top departure and arrival airports

[HighFlyerPL185]

Hello there,

I wanted to construct a little table on Pilot's Public Profile, showing their top 5 departure and arrival airports based on their PIREPs. I was wondering, how could I do that and what bits of code would be necessary to pick up the data? I'm struggling a bit since I guess it would have to check all of the pilot's PIREPs and somehow pick the information from them. I'd appreciate some suggestions from you if possible, please.

Kind Regards,

HighFlyer

[simpilot]

You could build your sql call to just get the pilots dep and arr airports and sort them by count highest to lowest. Maybe something like this for departures - untested but should get you started

$query = "SELECT COUNT(depicao) AS total FROM ".TABLE_PREFIX."pireps
			WHERE pilotid = '$pilotid'
			GROUP BY depicao
			ORDER BY total DESC";

$total = DB::get_results($query);


return $total->total;

[mattia]

dont work for me thx Dave

[simpilot]

I just tried this in a test install with a couple of adjustments and it seems to work correctly for me and returns an array of data

function

function favorite_dep($pilotid)   {
$query = "SELECT depicao ,COUNT(depicao) AS total FROM ".TABLE_PREFIX."pireps
  WHERE pilotid = '$pilotid'
  GROUP BY depicao
  ORDER BY total DESC";

$total = DB::get_results($query);

 return $total;
}

Returned array

array
 0 =>
object(stdClass)[57]
  public 'depicao' => string 'CYEG' (length=4)
  public 'total' => string '6' (length=1)
 1 =>
object(stdClass)[58]
  public 'depicao' => string 'CYYZ' (length=4)
  public 'total' => string '3' (length=1)
 2 =>
object(stdClass)[54]
  public 'depicao' => string 'CYYC' (length=4)
  public 'total' => string '3' (length=1)
 3 =>
object(stdClass)[53]
  public 'depicao' => string 'CYQT' (length=4)
  public 'total' => string '1' (length=1)
 4 =>
object(stdClass)[52]
  public 'depicao' => string 'CYHZ' (length=4)
  public 'total' => string '1' (length=1)
 5 =>
object(stdClass)[51]
  public 'depicao' => string 'PHOG' (length=4)
  public 'total' => string '1' (length=1)
 6 =>
object(stdClass)[50]
  public 'depicao' => string 'CYMM' (length=4)
  public 'total' => string '1' (length=1)
 7 =>
object(stdClass)[49]
  public 'depicao' => string 'CYQR' (length=4)
  public 'total' => string '1' (length=1)