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!

Improve this question
9
  • 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 Commented Jun 5, 2012 at 6:23
  • 1
    A properly-designed join should do it, failing that you'll have to resort to subqueries. Either way, both are well documented. Commented Jun 5, 2012 at 6:23
  • See the third example in this link: dev.mysql.com/doc/refman/5.0/en/sorting-rows.html Commented Jun 5, 2012 at 6:25
  • 1
    @JakeKing - but there should be threadID in Posts table. So JOIN them like @odiszpac wrote down then order by t.id, p.id Commented Jun 5, 2012 at 6:34
  • 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? Commented Jun 5, 2012 at 7:28

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

1 Comment

Very close, see my comment on the question.
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.

Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.