I'm working in with a database where I'm indexing aircraft and cross referencing with flights where the specific aircraft are assigned. Right now I have 3 tables... "flights", "aircraft", and "hangar".
The aircraft table holds information about each type of airframe we utilize... for example, "Boeing 737", or "Airbus 320" (plus it's specs... fuel, range, passenger loads, etc). Each record has a unique id.
The hangar table contains each individual aircraft in our fleet, and contains data on that particular aircraft such as how many total flight hours it has, it's registration number, serial number, last maintenance date, etc. Each record has a unique id also, and references the aircraft table to get it's make / model information.
The flights table contains all information related to our flights. This also has an aircraft id field, where the specific aircraft assigned to that flight is entered using the unique aircraft id from the hangar table.
What I'm attempting to do is create a drop down menu that lists all the individual aircraft in our hangar that are NOT assigned to a flight. This has proved to be a bit more difficult than I anticipated. The current code, after changing things here and there during the testing, is below :
// Create Hangar and Flight Query //
$ac = "SELECT hangar.hangar_id, hangar.hangar_reg, flight.flight_aircraft FROM hangar, flight WHERE hangar.hangar_id != 'flight.flight_aircraft' GROUP BY hangar.hangar_id ORDER BY hangar.hangar_reg DESC";
$ac_result = @mysql_query($ac, $connect) or die(mysql_error());
while ($acrow = mysql_fetch_array($ac_result)) {
$acid = $acrow['hangar_id'];
$acreg = $acrow['hangar_reg'];
// Create Aircraft Query //
$aca = "SELECT * FROM aircraft WHERE aircraft_id = '$acid'";
$acaresult = @mysql_query($aca, $connect) or die(mysql_error());
while ($acarow = mysql_fetch_array($acaresult)) {
$aca_id = $acarow['aircraft_id'];
$aca_type = $acarow['aircraft_type'];
$aca_name = $acarow['aircraft_name'];
}
$ac_list .= "<option value=$acid>$acreg $aca_type $aca_name</option>";
$ac_list_box = "
<select name=plane class=form>
<option value=9 selected>Select One</option>
<option value=9>--------------------</option>
$ac_list
</select>";
}
Unfortunately, this isn't working as I'd planned. What I'm getting is a drop down menu that looks like this :
Select One
--------------------
N048FE
N1987G Boeing 737
N802DS Boeing 727
N810JU Boeing 737
I don't understand why the first plane in the menu is not showing it's type and name. If I change the ORDER BY to ASC in the first SQL query, then the first plane is N810JU... but it will still show up without it's type and name. No matter how I ORDER or GROUP the SQL query the first plane always has a missing type and name.
Naturally I'm not doing something right, but I can't seem to find out where I went wrong. Can anyone give a look over and give me an idea, or a suggestion or two?
Thanks...
