1

I'm building some very simple forum software as a sort of self-test for my PHP and MySQL skills, but I'm not quite sure how to accomplish this task. I have a table called Threads which contains a list of all threads. I also have a table called Posts, which contains all posts. Each table's primary key is an auto-increment ID. Each row in Posts also contains the ID of the thread it belongs to.

While I can easily retrieve all of the threads in a certain subforum with a query like this:

SELECT * FROM Threads WHERE ForumID='$forumid'

...I'm not sure how to sort that based on the latest post from each thread. I can get the posts for any given thread like this:

SELECT * FROM Posts WHERE ThreadID='$threadid'

...but I don't know how to incorporate that data into my initial query to sort it. Since each post has a unique ID, posts with higher IDs will always be more recent, so there's no need to compare dates or anything like that. I'm just unclear on how to actually do the query.

I'm pretty sure that this is possible in just MySQL, but if it's not, what would be the most efficient PHP solution? Thanks!

Alexis King
  • 43,109
  • 15
  • 131
  • 205
  • Can you add the basic schema for `Threads` and `Posts` table, so that we can test the query too! I think it should work with a combination of columns in ORDER BY clause – Ozair Kafray Jun 05 '12 at 06:23
  • 1
    A properly-designed join should do it, failing that you'll have to resort to subqueries. Either way, both are well documented. – GordonM Jun 05 '12 at 06:23
  • See the third example in this link: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html – Ozair Kafray Jun 05 '12 at 06:25
  • @OzairKafray I can't do that since I don't actually have postID until I get the data from the Posts table. There is no postID in the Threads table. – Alexis King Jun 05 '12 at 06:32
  • 1
    @JakeKing - but there should be threadID in Posts table. So JOIN them like @odiszpac wrote down then `order by t.id, p.id` – Joe Jun 05 '12 at 06:34
  • @Jake King: Exactly as Joe has said in the above comment. – Ozair Kafray Jun 05 '12 at 06:40
  • 1
    I tried that, and it works, but with one problem. The `ORDER BY` is applied first, then `DISTINCT` removes duplicates. However, it removes the duplicates from the *beginning*, not the end, and so it fails. Is there any way to reverse this behavior? – Alexis King Jun 05 '12 at 07:28
  • @Ozair Or am I doing this incorrectly? The problem is, I'm generating a list of entries which are correct, but after duplicate elimination with `DISTINCT`, the order is wrong. – Alexis King Jun 05 '12 at 07:38
  • @Jake King - I think your approach is right. In the column selection part of your query add max(postId) as maxpostid, then have your ORDER BY maxpostid – Ozair Kafray Jun 05 '12 at 09:59

4 Answers4

2

Try this with INNER JOIN 

SELECT p.*,t.*
FROM Threads t 
INNER JOIN Posts p ON t.id = p.ThreadID
ORDER BY
//whatever column you want like this p.date
p.date
DESC
Query Master
  • 6,989
  • 5
  • 35
  • 58
1

Thomething like this can helps you

SELECT DISTINCT t.* FROM Posts p LEFT JOIN Threads t ON p.ThreadID = t.id ORDER BY p.last_modified_time DESC

Or

SELECT DISTINCT t.* FROM Posts p LEFT JOIN Threads t ON p.ThreadID = t.id ORDER BY p.id DESC
odiszapc
  • 4,089
  • 2
  • 27
  • 42
1

A simple JOIN statement is the solution:

SELECT *
FROM Threads t
JOIN Posts p ON t.ThreadID = p.ThreadID
ORDER BY t.ModificationDate DESC -- or whatever column you want

This gives you the latest posts over all threads.

CodeZombie
  • 5,367
  • 3
  • 30
  • 37
0

All of the given answers were very close, but I ended up using the following query:

SELECT t.*
FROM Threads t
INNER JOIN Posts p ON t.id = p.threadId
GROUP BY t.id
ORDER BY MAX(p.id) DESC

This is due to DUPLICATE causing problems due to its auto-grouping. See this answer for more info on the topic.

Community
  • 1
  • 1
Alexis King
  • 43,109
  • 15
  • 131
  • 205