0

This is my first time here on Stack. I'm attempting to build a message board (forum) using PHP and MySQL. I'd like to display a table in PHP with the following information:

  1. one column displaying all current forums
  2. another column displaying the total number of threads and posts per forum.
  3. last column for displaying the latest thread created.

This is the query I have that returns the correct latest thread, but not the correct count:

SELECT 
       forums.*,
       threads.thread_id,
       threads.thread_topic,
       threads.user_id,
       users.user_name, 
       threads.thread_date, 
       COUNT(DISTINCT threads.thread_id) AS thread_count,
       COUNT(DISTINCT posts.post_id) AS post_count
   FROM forums 
   LEFT OUTER JOIN threads
      ON forums.forum_id = threads.forum_id
      AND threads.thread_date = 
         (SELECT MAX(threads.thread_date) AS last_topic
             FROM threads 
             WHERE forums.forum_id = threads.forum_id)
   LEFT JOIN users 
      ON users.user_id = threads.user_id
   LEFT JOIN posts 
      ON threads.thread_id = posts.thread_id
   GROUP BY forums.forum_id

These are the results of the post and thread counts, it's only counting the threads and posts in the latest thread of each forum:

post count  thread count
    -             -
    7             1
    1             1
    1             1
    1             1
    0             0

This query gets the correct post and threads counts for each forum:

SELECT 
       COUNT(DISTINCT posts.post_id) AS post_count, 
       COUNT(DISTINCT threads.thread_id) AS thread_count
   FROM forums 
   LEFT OUTER JOIN threads 
      ON threads.forum_id = forums.forum_id
   LEFT JOIN posts 
      ON threads.thread_id = posts.thread_id
   GROUP BY forums.forum_id;

The results for the post and thread count for each forum should be:

post count  thread count
        -             -
        7             1
        1             1
        3             2
        1             1
        0             0

Is there anyway I can combine the two queries, or fix the first one, so I get the correct output?

Please use the following tables in your suggestions:

users, threads, forums, posts

I'm new to PHP and MySQL, so I'm sorry if it's not the right way to go about doing something like this.

Your help is very much appreciated!

OhBeWise
  • 5,350
  • 3
  • 32
  • 60
Emmuls
  • 3
  • 1
  • You're only counting the latest thread, because of the `AND threads.threaddate = (SELECT MAX(threads.threaddate) ...)` condition. – Barmar May 01 '15 at 17:27
  • You need to join with a subquery that gets the row for the latest thread in each forum. See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group for how to write that subquery. – Barmar May 01 '15 at 17:30
  • Yes, I understand. I just was not sure how to format the subquery correctly. Thank you, for the link. – Emmuls May 01 '15 at 19:50

1 Answers1

0
SELECT
    forums.*,
    counts.thread_count,
    counts.post_count,
    threads.*,           -- last thread
    users.*              -- user of last thread
FROM forums
LEFT JOIN (
    SELECT 
        forums.forum_id,
        COUNT(DISTINCT threads.thread_id) AS thread_count,
        COUNT(DISTINCT posts.post_id) AS post_count
    FROM forums 
    LEFT JOIN threads ON threads.forum_id = forums.forum_id
    LEFT JOIN posts ON posts.thread_id = threads.thread_id
    GROUP BY forums.forum_id
) counts ON counts.forum_id = forums.forum_id
LEFT JOIN (
    SELECT
        forums.forum_id,
        threads.thread_id,
    FROM forums
    JOIN threads ON forums.forum_id = threads.forum_id
    AND threads.thread_date = 
        (SELECT MAX(threads.thread_date) AS last_topic
            FROM threads 
            WHERE forums.forum_id = threads.forum_id)
) last_thread ON last_thread.forum_id = forums.forum_id
LEFT JOIN threads ON threads.thread_id = last_thread.thread_id
LEFT JOIN users ON users.user_id = threads.user_id
isevcik
  • 553
  • 1
  • 4
  • 15