[SOLVED] grouping question

[smerny]

	$search = "SELECT * FROM messages WHERE ID_to = '".$user_id."' GROUP BY ID_convo ORDER BY time DESC";
$result = mysql_query($search) or die ("SQL Error: ". mysql_error());
while ($messages = mysql_fetch_array($result))
{
	$search = "SELECT DisplayName FROM users WHERE ID = '".$messages['ID_from']."' LIMIT 1";
	$result2 = mysql_query($search) or die ("SQL Error: ". mysql_error());
	$users = mysql_fetch_array($result2);

	echo "<tr>
	<td>". $users['DisplayName'] ."</td>
	<td>". $messages['subject'] ."</td>
	<td>". $messages['time'] ."</td>";
}

 

This is only giving me the information from the FIRST message with the message convo ID, I want it to only give me the LAST message with the message convo ID

[kickstart]

Hi

 

Think you need some SQL similar to this to get the latest record.

 

SELECT *

FROM (SELECT ID_convo, MAX(time) AS msgTime FROM messages WHERE ID_to = '".$user_id."' GROUP BY ID_convo) a

INNER JOIN messages b

ON a.ID_convo = b.ID_convo

AND a.msgTime = b.time

 

All the best

 

Keith

[smerny]

worked good, but backwards... added "ORDER BY msgTime DESC"

 

thanks