Advanced MySQL ORDER BY

I am making a basic forum using PHP and MySQL. I am using one table for all of the threads. It is named forum_posts开发者_开发百科. It has these fields:

id, creator, time, title, message, thread_reply, forum_id, locked, sticky

If the post is a new thread, then thread_reply is set to 0. Otherwise, thread_reply is set to the id of the thread that this post is replying to. On the page that shows all of the threads, I want to order the threads by the time of the last post that was replied to it. I am using this query:

SELECT `id`, `title`, `creator` FROM `forum_posts` WHERE `thread_reply` = 0 AND `forum_id` = 1 ORDER BY -`time`

The only problem is that query only orders by the time the thread was created, instead of the time the last reply was to the thread. If you don't understand what I am talking about, go look at the home page of any forum and look how it orders the threads by the time of the last post to the thread, not the time the thread was created.

---

I made a custom forum last week (as phpbb just didn't suit my needs).

I would first advise the following structure:

• table for users
• table for categories (main sections of forum)
• table for threads (these are inside categories)
• table for posts (these are in individual threads)

To order the posts by the last action, I simply created a field in the Threads table that was called last_action or something along those lines.

Then, when the thread is first created, I set that value to the date it was created. Then, whenever a post is edited or a new post is added to that thread, that value updates. This then means you just do:

SELECT blah, blah, blah FROM threads WHERE cat=4 ORDER BY last_action DESC

Hopefully that helps you.

---

Easiest way is probably to make it so that instead of using the test thread_reply=0, you use the test thread_reply=id. Make a thread starting-thread be a reply to itself. If you use that for something else it might make it a bit more messy, but you can thus do something like

SELECT * FROM
    (SELECT `id`, `title`, `creator`, b.rtime FROM `forum_posts`
        WHERE a.`thread_reply` = a.`id` AND `forum_id` = 1 ) a
    LEFT JOIN (SELECT `thread_reply`, MAX(`time`) rtime FROM `forum_posts` 
        WHERE `forum_id`=1
        GROUP BY `thread_reply`) b ON a.`id`=b.`thread_reply`
    ORDER BY `rtime` DESC

Note that the first subquery is unnecessary, however it is probably more efficient to first filter by forum and "is it a first post".

Also, why use ORDER BY -time ASC when you could use ORDER BY time DESC?

But yes, the best way is probably just to have a table for topics, and a table for replies. (First reply in a topic is the first post; the topic itself has no inherent body text)

---

Use

SELECT `op`.`id`, `op`.`title`, `op`.`creator`
FROM `forum_posts` `op`, `forum_posts` `rep`
WHERE (`op`.`thread_reply` = 0 OR `op`.`thread_reply` = `rep`.`id`)
    AND `op`.`forum_id` = 1
ORDER BY IF(`op`.`thread_reply` = 0, -`op`.`time`, -`rep`.`time`)

Basically what I do here is find the reply if applicable and order by its time.

---

If you have the possiblity:

Create two tables:

Threads: id, subject, date_created, date_last_reply, ..

Posts: id, thread_id, date, ..

SELECT * FROM threads ORDER BY date_last_reply

---

As you're only getting the "start" post with that query you've got no way to order by the latest reply. You need to add another column (last_reply_time etc) and have your application update that field every time a new thread is posted, and then order by that.