MySQL LEFT JOIN, INNER JOIN etc, complicated query, PHP + MySQL for a forum
- by Sven Eriksson
So I've got a little forum I'm trying to get data for, there are 4 tables, forum, forum_posts, forum_threads and users. What i'm trying to do is to get the latest post for each forum and giving the user a sneak peek of that post, i want to get the number of posts and number of threads in each forum aswell. Also, i want to do this in one query. So here's what i came up with:
SELECT lfx_forum_posts.*, lfx_forum.*, COUNT(lfx_forum_posts.pid) as posts_count,
lfx_users.username,
lfx_users.uid,
lfx_forum_threads.tid, lfx_forum_threads.parent_forum as t_parent,
lfx_forum_threads.text as t_text, COUNT(lfx_forum_threads.tid) as thread_count
FROM
lfx_forum
LEFT JOIN
(lfx_forum_threads
INNER JOIN
(lfx_forum_posts
INNER JOIN lfx_users
ON lfx_users.uid = lfx_forum_posts.author)
ON lfx_forum_threads.tid = lfx_forum_posts.parent_thread AND lfx_forum_posts.pid =
(SELECT MAX(lfx_forum_posts.pid)
FROM lfx_forum_posts
WHERE lfx_forum_posts.parent_forum = lfx_forum.fid
GROUP BY lfx_forum_posts.parent_forum)
)
ON lfx_forum.fid = lfx_forum_posts.parent_forum
GROUP BY lfx_forum.fid
ORDER BY lfx_forum.fid ASC
This get the latest post in each forum and gives me a sneakpeek of it, the problem is that
lfx_forum_posts.pid =
(SELECT MAX(lfx_forum_posts.pid)
FROM lfx_forum_posts
WHERE lfx_forum_posts.parent_forum = lfx_forum.fid
GROUP BY lfx_forum_posts.parent_forum)
Makes my COUNT(lfx_forum_posts.pid) go to one (aswell as the COUNT(lfx_forum_threads.tid) which isn't how i would like it to work. My question is: is there some somewhat easy way to make it show the correct number and at the same time fetch the correct post info (the latest one that is)?
If something is unclear please tell and i'll try to explain my issue further, it's my first time posting something here.
