Jump to content


Photo

Selecting certain records and displaying them


  • Please log in to reply
2 replies to this topic

#1 hellouthere

hellouthere
  • Members
  • PipPipPip
  • Advanced Member
  • 76 posts

Posted 20 October 2006 - 07:10 PM

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...
$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>";
	
		}

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...

I'm Not Flying, I'm Falling With Style

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 20 October 2006 - 07:52 PM

Not tested as I haven't the data but try
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)


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 hellouthere

hellouthere
  • Members
  • PipPipPip
  • Advanced Member
  • 76 posts

Posted 28 October 2006 - 11:02 AM

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!

I'm Not Flying, I'm Falling With Style




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users