Selecting certain records and displaying them

[hellouthere]

could do with some help for a script...

I have a table called 'aircraft' with fields... air_id, air_type, reg, status... air_id and reg are unique.

I have another table called 'reports' with fields like report_id, pilot_id, reg, destination_id.

Basically i need to select the most recent report for each 'aircraft' registration and display the 'destination_id' in a table.

This is the code i have for displaying the table so far...
[code]
$querya = "SELECT * FROM aircraft";
$resulta = mysql_query($querya);


$numbera = mysql_num_rows($resulta);

if ($numbera > 0) {

print "<table>";
print "<tr>";
print "<td bgcolor=#7B85DC width=90 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>Aircraft</b></font></td>";
print "<td bgcolor=#7B85DC width=90 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>Registration</b></font></td>";
print "<td bgcolor=#7B85DC width=90 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>Status</b></font></td>";
print "<td bgcolor=#7B85DC width=90 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>AF Hours</b></font></td>";
print "<td bgcolor=#7B85DC width=90 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>Location</b></font></td>";
print "</tr>";


for ($s=0; $s<$numbera; $s++) {
    $air = mysql_result($resulta,$s,"air_type");
    $reg = mysql_result($resulta,$s, "reg");
    $status = mysql_result($resulta,$s, "status");
$afid = mysql_result($resulta,$s, "air_id");

$query_afloc = "SELECT r1.registration, r1.destination_id
FROM reports AS r1
LEFT JOIN reports AS r2 ON r1.registration = r2.registration
                      AND r1.report_id < r2.report_id
WHERE r2.report_id IS NULL
ORDER BY registration";
$result_afloc = mysql_query($query_afloc);
$afloc = mysql_result($result_afloc,$s,"destination_id");

print "<tr>";
    print "<td width=90 height=12 align=left><font face=Arial size=1 color=#313398>$air</font></td>";
    print "<td width=90 height=12 align=left><font face=Arial size=1 color=#313398>$reg</font></td>";
if ($status == 'act') {
    print '<td width=90 height=12 align=left><img alt="Active" src="images/acstgr.gif"</img></td>';
}else if ($status == 'main') {
print '<td width=90 height=12 align=left><img alt="Maintenance" src="images/acstbl.gif"</img></td>';
}else if ($status == 'oos') {
print '<td width=90 height=12 align=left><img alt="Out Of Service" src="images/acstre.gif"</img></td>';
}
    print "<td width=90 height=12 align=left><font face=Arial size=1 color=#313398>$hour</font></td>";
    print "<td width=90 height=12 align=left><font face=Arial size=1 color=#313398>$afloc</font></td>";
    print "</tr>";
}
print "</table>";

}
[/code]

As you can see i have tried to do this already but without success... It seems to take a destination_id for each reg but not the right one... Any Help Is apreciated...

[Barand]

Not tested as I haven't the data but try
[code]
SELECT a.air_type, a.reg, a.status, r.destination_id
FROM aircraft a INNER JOIN reports r ON a.reg = r.reg
WHERE r.report_id = (SELECT MAX(r2.report_id) FROM reports r2 WHERE r2.reg = r.reg)

[/code]

[hellouthere]

That seems to work to a certain degree... when i display the table i get destination_id's displaying but not for the right aircraft... i only have a few aircraft with reports so far... the top 2 aircraft in the table have the location displayed by them regardless of what the registration is... this is really confusing me now i cant see anything wrong with any of the code!