Order by latest post conundrum

[joe92]

I'm trying to create a basic forum to pin onto my site.

 

I've hit a problem with the page which displays all the threads of a certain board.

 

Similarly to phpBB and many others, I want the threads/topics to be ordered by the latest post. I am having trouble with this query

 

//get all the thread id's from this board
$thread_query = mysql_query("SELECT DISTINCT threadID FROM forum_posts WHERE boardID = '$bid' ORDER BY postTime");

 

The above query only gets the 1st unique threadID and ignores the rest meaning the order is incorrect.

 

How can I rewrite this query so it will select the unique thread ID's, but order the thread ID's by the latest postTime?

 

Cheers, Joe

[Muddy_Funster]

Remove the word DISTINCT from the query and  - ORDER BY postTime DESC.

 

curious though - why are you trying to re-invent the wheel so to speek?

[kickstart]

Hi

 

Do you just have a table of posts, or do you have a table of threads as well?

 

$thread_query = mysql_query("SELECT threadID, MAX(postTime) AS MaxPostTime FROM forum_posts WHERE boardID = '$bid' GROUP BY threadID ORDER BY MaxPostTime");

 

All the bst

 

Keith

[PFMaBiSmAd]

only gets the 1st unique threadID

 

^^^ I'm going to guess that your php code isn't looping over the result set, but is instead just fetching the first row from the result set.

[joe92]

$thread_query = mysql_query("SELECT threadID, MAX(postTime) AS MaxPostTime FROM forum_posts WHERE boardID = '$bid' GROUP BY threadID ORDER BY MaxPostTime");

 

Thank you very much! Worked a treat. I just needed to add a DESC to the end to get my desired result:

$thread_query = mysql_query("SELECT threadID, MAX(postTime) AS MaxPostTime FROM forum_posts WHERE boardID = '$bid' GROUP BY threadID ORDER BY MaxPostTime DESC");

 

[attachment deleted by admin]

[joe92]

p.s. How do I mark this as solved?

 

Never mind, found it.