Need help with 2 issues

[me2]

Hi all, I'm hoping someone here can help me with a couple of issues, hopefully I can explain things clearly.

 

I'm trying to build a fairly basic forum with php & mysql that I can integrate with the rest of the site I'm building, I've been trying to build the forum index and have managed to do it but the way I've done it is my concern, my sql skills aren't great so I would like some help with the queries if possible.

 

Basically the index page looks the same as most forums & has 3 columns:  forum name & forum discription - thread & post count - last post. To display this layout I need to work with 3 db tables: forums, forum_threads & forum_posts. The forums table has the fields: forum_id, forum_name, forum_description & forum_category, the forum_threads table has: thread_id, thread_subject, thread_date, thread_forum & thread_by, the forum_posts table has: post_id, post_content, post_date, post_thread & post_by.

 

The problem I've had concerns the 'last post' part of the forum, obviously I need to fetch: thread_id, thread_subject, post_by & post_date. But of course this all needs to be related to the last post of that particular forum. So thread_id needs to be the thread that the last post appeared in etc etc, you get the point. I think I can get the correct post_date just by doing: MAX(post_date), but I would still need to sort out the other 3 things.

 

As I've already said, my sql skills aren't great but I'm assuming this can be done using joins or maybe joins & subqueries. I've managed to put together a query that does seem to work, but I expect it's most probably a poor way of doing it. This is what I have:

 

 

SELECT forum_id, forum_name, forum_description,

 

(SELECT thread_id FROM forum_threads, forum_posts WHERE thread_id = post_thread

  AND thread_forum = forum_id ORDER BY post_date DESC LIMIT 1) AS thread_id,

 

(SELECT thread_subject FROM forum_threads, forum_posts WHERE thread_id = post_thread

  AND thread_forum = forum_id ORDER BY post_date DESC LIMIT 1) AS thread_subject,

 

(SELECT post_by FROM forum_threads, forum_posts WHERE thread_id = post_thread

  AND thread_forum = forum_id ORDER BY post_date DESC LIMIT 1) AS posted_by,

 

MAX(post_date), COUNT(DISTINCT thread_id) AS threads, COUNT(DISTINCT post_id) AS posts

 

FROM forums LEFT JOIN forum_threads ON forum_id = thread_forum LEFT JOIN forum_posts ON thread_id = post_thread

WHERE forum_category = '$category' GROUP BY forum_id

 

 

This query does what I want but is there a better way? Probably using a more complex join or something?

 

 

Last question: how can I display forums in categories without having to place a query within a loop? So for example, display category heading 1 -> list forums 1-5, display category heading 2 -> list forums 6-10 and so forth...

 

The way I'm doing it at the moment is to store categories in a table called forum_categories, this table has the fields cat_no, cat_name. I order the categories by cat_no so that if I wish to change the order in which their displayed I can just change the numbers.

 

So, I'll fetch all the categories from this table with a loop, and within this loop I display the cat heading & then run another query which then fetches the forums that match that category. This works fine but of course if I have 3 categories it then has to perform 3 separate queries to fetch all the forums. Is there a better way of doing this? Quite clearly the way I'm doing it means that the more categories you have the more queries you have to run, I assuming that there must be a better way.

 

So, at the moment it takes 5 separate queries to display this page. 1 query for getting the categories, 3 queries for getting the forums as I have 3 categories & 1 query for displaying the total threads/post on the whole forum.

 

If anyone can give me better examples of doing these things or point me in the right direction it would be much appreciated, thanks.

[fenway]

TLDR.