hellouthere Posted October 20, 2006 Share Posted October 20, 2006 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_idFROM reports AS r1LEFT JOIN reports AS r2 ON r1.registration = r2.registration AND r1.report_id < r2.report_idWHERE r2.report_id IS NULLORDER 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... Link to comment https://forums.phpfreaks.com/topic/24584-selecting-certain-records-and-displaying-them/ Share on other sites More sharing options...
Barand Posted October 20, 2006 Share Posted October 20, 2006 Not tested as I haven't the data but try[code]SELECT a.air_type, a.reg, a.status, r.destination_idFROM aircraft a INNER JOIN reports r ON a.reg = r.regWHERE r.report_id = (SELECT MAX(r2.report_id) FROM reports r2 WHERE r2.reg = r.reg)[/code] Link to comment https://forums.phpfreaks.com/topic/24584-selecting-certain-records-and-displaying-them/#findComment-112034 Share on other sites More sharing options...
hellouthere Posted October 28, 2006 Author Share Posted October 28, 2006 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! Link to comment https://forums.phpfreaks.com/topic/24584-selecting-certain-records-and-displaying-them/#findComment-115782 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.