PHP + MySQL Forum display

Question

I am currently building a simple PHP + MySQL forum but I am having problems with getting the information to show in the correct format.

My current SQL code is

SELECT forum_posts.catId, forum_posts.postId, forum_posts.date, forum_posts.message,
    forum_posts.userId, users.userId, users.username, forum_thread.threadId, forum_thread.subjectTitle
    FROM forum_posts
    LEFT JOIN forum_thread ON forum_posts.threadId = forum_thread.threadId
    LEFT JOIN users ON users.userId = forum_posts.userId
    GROUP BY forum_posts.catId
    ORDER BY forum_posts.postId DESC, forum_posts.date DESC, forum_posts.catId ASC

The problem I have is that it brings back everything in the right category but it brings back the first result of the category and not the last one.

I simply want the code to show the last reply in each category.

Any help is much appreciated thank you.

score 1 · Answer 1 · edited May 23 '17 at 12:03

Your query should return a range of rows. Try to limit the result to 1 element. If you sort the results descending, you will get the last item.

 ORDER BY ... DESC LIMIT 1

I am not sure whether you find the latest entry by postId or date. If you find it by date, you must start the grouping with the date. But I don't understand why you are sorting the results so much for getting only one dataset.

 ORDER BY forum_posts.date DESC LIMIT 1;

Is this what you want? Additionally this could help you: Select last row in MySQL.

PHP + MySQL Forum display

1 Answers1