How to Combine 2 SQL Codes Together?

phpVMS Legacy Support (v2/v5)
1

Can anyone give me a quick explanation on how to combine 2 SQL codes together? I’m fine with HTML and PHP though I’m hopeless with SQL.

What I’m Trying to Do:

Combine:

public static function GetAllAircraft($onlyenabled=false)
{
	$sql = 'SELECT * 
				FROM ' . TABLE_PREFIX .'aircraft';

	if($onlyenabled == true)
	{
		$sql .= ' WHERE `enabled`=1 ';
	}

	$sql .= ' ORDER BY icao ASC';

	return DB::get_results($sql);
}

and:

public static function AircraftUsage()
{
	$sql = 'SELECT a.name AS aircraft, a.registration, 
				SEC_TO_TIME(SUM(TIME_TO_SEC(p.flighttime))) AS hours, 
				COUNT(p.distance) AS distance
			FROM '.TABLE_PREFIX.'pireps p
				INNER JOIN '.TABLE_PREFIX.'aircraft a ON p.aircraft = a.id
			GROUP BY p.aircraft';

	return DB::get_results($sql);
	return $ret;
}

And call it FleetInfo

Can anyone help me?

Thanks,

Mitch

2

I’m not sure what you mean by combine - what are you trying to get in the end?

3

This is my problem:

I am working on my Fleet Table 1.1, I need total hours and flights etc. which is located in StatsData, and I need all the other aircraft info (registration, total pax, etc.) which is located in AircraftData to be in one. So I can use data from both in one single array.

So eventually it should end up like this though, with it working

$sql = Aircraft Data:Getallaircraft SQL stuff

StatsData:AircraftUsage stuff

4

You can modify the query to be:

$sql = 'SELECT  a.*, a.name AS aircraft, a.registration,

(notice the a.*), and that will give you what you want

5

So you talking about modifying the AircraftUsage SQL?

6

You can just create your own function within your module which does the same thing

7

Test

8

Just get a foreach error…no data is being returned. My code:

9

You don’t need that second SELECT * FROM aircraft that a.* in the query will return all the aircraft data

10

Thanks! Now I got it working,

Next Question (sorry)

Is it possible to get the last flight flown by that aircraft’s PIREP id? Then I can just use GetReportDetails($pirepid) to get some more information.

^^

What i mean is that can i get the last filed PIREP for that aircraft? Then get the PIREP id for it?

Thanks in advance,

Mitch

11

Don’t mean to be rude, but bump…

12

http://docs.phpvms.net/development/searching_schedules_and_pireps

The doc isn’t finished, but the same premise for PIREPS, using the search interface. It will return all the data about a PIREP for whichever parameters you pass.

You can see the query and the fields returned:

http://bugs.phpvms.net/browser/trunk/core/common/PIREPData.class.php#L44

So you can search by the fields by putting them in an array:

<?php

$params = array(
  'aircraftid' => [iD],
  // or you can do:
  'registration' => [registration],
);

// Just set the sort order to the last one
// save the old value
$old_sort_order = Config::Get('PIREPS_ORDER_BY');
Config::Set('PIREPS_ORDER_BY', 'submitdate DESC');

// Now find the PIREPS, retrieve one
$pirep = PIREPData::findPIREPS($params, 1);
$pirep = $pirep[0];

// And reset the sort order to our setting:
Config::Set('PIREPS_ORDER_BY', $old_sort_order);