[SOLVED] Another display problem... for Barand again...

[hellouthere]

I am trying to take the most recent flightid for each callsign in the database... this is the query i have, adapted from the las one Barand gave me...

 

		$lstsql = "SELECT p.realname, h.IDPIREP, h.CreatedON
	FROM pilots AS p LEFT JOIN pirep AS h ON p.ID = h.IDPilot
	WHERE pos!=''
	GROUP BY p.realname
	ORDER BY p.ID";

 

this is after i print the table header...

 

for ($i=0; $i<$number; $i++) {

   list($lstname, $lstid, $lstdate) = mysql_fetch_row($lstres);

 

i ave a pirep table with IDPIREP, IDPilot and CreatedON fields... these are the only relevant ones...

 

also a pilots table with ID (=IDPilot in pirep table)

 

the query above returns the oldest result...

 

Thanks in advance...

[Barand]

If IDPirep is an auto_increment field then the highest will be the latest.

 

SELECT p.ID, p.realname, MAX(h.IDPirep) as latest
FROM pilot p INNER JOIN pirep h ON p.ID = h.IDPilot
GROUP BY p.ID

[hellouthere]

could i then use

 

for ($i=0; $i<$number; $i++) {
$latest = mysql_result($lstres,$i, "latest");

 

to acess it...

[Barand]

You could, but mysql_result is the slowest way to access a result set, mysql_fetch_row() is the fastest.

 

<?php

$lstsql = "SELECT p.ID, p.realname, MAX(h.IDPirep) as latest
            FROM pilot p INNER JOIN pirep h ON p.ID = h.IDPilot
            GROUP BY p.ID";
$lstres = mysql_query($lstsql) or die (mysql_error().'<p>$lstsql</p>');

while (list($id, $pilot, $latest) = mysql_fetch_row($lstres)) {
    echo "$id $pilot $latest <br/>";
}	
?>

[hellouthere]

thanks Barand, saved my life again...