[SOLVED] order by the max value in another table

[smerny]

	$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
WHERE topics.ID_board='$board_id'
ORDER BY postSub.LatestPostTime DESC";
$result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error());
while($topics = mysql_fetch_array($result))
{

Making a forum, trying to get the topics to be ordered with the one with the latest post first... this is the code I was trying to work it... and it's not working... it is in the order in which the topic was created

 

[fenway]

What's not working... show output.

[smerny]

trying to get the topics to be ordered with the one with the latest post first... but it is in the order in which the topic was created

[fenway]

Well, the query looks like it should be OK.... have you checked the output values of created and pasttime?

[smerny]

	$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
WHERE topics.ID_board='$board_id'
ORDER BY postSub.LatestPostTime DESC";
$result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error());
while($topics = mysql_fetch_array($result))
{
echo $topics['LatestPostTime'] ." Latest Post Time -- ". $topics['ID'];

 

gives me...

 

2009-07-15 12:12:09 Latest Post Time -- 1

2009-07-15 12:12:09 Latest Post Time -- 2

2009-07-15 12:12:09 Latest Post Time -- 3

2009-07-15 12:12:09 Latest Post Time -- 4

2009-07-15 12:12:09 Latest Post Time -- 10

 

when it should give me...

 

2009-07-15 12:12:09 Latest Post Time -- 10

2009-07-15 11:24:57 Latest Post Time -- 1

2009-07-08 16:44:46 Latest Post Time -- 4

2009-07-08 14:23:03 Latest Post Time -- 2

2009-07-07 22:46:44 Latest Post Time -- 3

[kickstart]

Hi

 

Not sure of the exact layout of your tables, but I presume that ID_board is the id of a particular forum on your board (ie, like this one is MySQL Help).

 

If so you appear to be getting the latest post for any thread on the board you are looking at (so all threads would land up with the same latest post date).

 

All the best

 

Keith

[smerny]

the ID is actually a number, there is another table in the DB for boards which has one field being "ID" which increments, and both the table for posts and for topics have a field called "ID_board" which specifies which board the topic/post belongs to

 

on this site, I believe the ID of MySQL Help is 3.0

[kickstart]

Hi

 

Think you need the id of the topic in the join. Something like this (guessing at column names):-

 

$search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime

FROM topics

LEFT OUTER JOIN (SELECT ID_topic, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_topic) postSub

ON topics.ID = postSub.ID_topic

WHERE topics.ID_board='$board_id'

ORDER BY postSub.LatestPostTime DESC";

$result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error());

while($topics = mysql_fetch_array($result))

{

echo $topics['LatestPostTime'] ." Latest Post Time -- ". $topics['ID'];

 

All the best

 

Keith

[smerny]

oh dang, now i get what you were saying before. thanks a lot