Can't create separate forum categories based on SQL output

Question

I am facing a bit of a problem and I am really not sure what I may be doing wrong. What I am trying to do is to create a proprietary forum that I can use for my clients and for my own projects. I agree that it's plain silly trying to reinvent the wheel instead of just ripping phpBB apart but this is also a good way for me to learn on my mistakes.

Problem The problem is in the fact that the PHP script loop's the categories. For example we have 3 categories in SQL. Cookies, Cakes and Coffee now we have 2 forums for each say Like and Dislike in a proper forum they would look as such

Cookies

• Like
• Dislike

Cakes

• Like
• Dislike

And so on. However in mine it would look as such:

Cookies

• Like

Cookies

• Dislike

Code Ok here is the code for StackOverflow folks to rip appart and explain to me what I am doing wrong or what I may have missed or what I should do instead to make it function as it was intended to do.

<?php
//create_cat.php
include 'connect.php';
include 'header.php';

$sql = "SELECT
            categories.cat_id,
            categories.cat_name,
            forums.forum_id,
            forums.forum_cat,
            forums.forum_name,
            forums.forum_desc,
            forums.forum_admin,
            COUNT(forums.forum_id) AS forums
        FROM
            categories
        LEFT JOIN
            forums
        ON
            forums.forum_cat = categories.cat_id
        GROUP BY
            forums.forum_name, forums.forum_desc, forums.forum_id
        ORDER BY
            categories.cat_id ASC
        ";

$result = mysql_query($sql);

if(!$result)
{
    echo 'The categories could not be displayed, please try again later.';
}
else
{
    if(mysql_num_rows($result) == 0)
    {
        echo 'No categories defined yet.';
    }
    else
    {
        //prepare the table         
        while($row = mysql_fetch_assoc($result))
        {       

        echo '<table border="1">
              <tr>
                <th>' . $row['cat_name'] . '</th><th></th>
                </tr>';     
            echo '<tr>';

                if ($_SESSION['user_level'] != 9 AND $row['forum_admin'] == 1) { 

                echo "<div style='padding:8px;background-color:#fae7af;'>Sorry but you this is for Admins only.</div><br>";

                } else {

                echo '<td class="leftpart">';
                    echo '<h3><a href="viewforum.php?f=' . $row['forum_id'] . '">' . $row['forum_name'] . '</a></h3>' . $row['forum_desc'];
                echo '</td>';
                echo '<td class="rightpart">';

                //fetch last topic for each cat
                    $topicsql = "SELECT
                                    topic_id,
                                    topic_subject,
                                    topic_date,
                                    topic_cat
                                FROM
                                    topics
                                WHERE
                                    topic_cat = " . $row['forum_id'] . "
                                ORDER BY
                                    topic_date
                                DESC
                                LIMIT
                                    1";

                    $topicsresult = mysql_query($topicsql);

                    if(!$topicsresult)
                    {
                        echo 'Last topic could not be displayed.';
                    }
                    else
                    {
                        if(mysql_num_rows($topicsresult) == 0)
                        {
                            echo 'no topics';
                        }
                        else
                        {
                            while($topicrow = mysql_fetch_array($topicsresult))
                            echo '<a href="viewtopic.php?t=' . $topicrow['topic_id'] . '">' . $topicrow['topic_subject'] . '</a> at ' . date('d-m-Y', strtotime($topicrow['topic_date']));
                        }
                    }
                echo '</td>';

                }

            echo '</tr>';
            echo '</br>';
        }
    }
}

include 'footer.php';
?>

SQL

**Category**
cat_id     | int(8) | primary | auto incr
cat_name   | var(255)

|2|Test 2
|3|Test 3
|1|Test 1


**Forum**
forum_id   | int(8) | primary | auto_incr
forum_cat  | int(8) <-- forum cat "category" is just ID of category it belongs to
forum_name | var(255)
forum_desc | var(255)

|1|1|Test|Just a simple forum test
|2|3|More Test | Just a 2nd test forum
|3|1|Bugs|Bugs and related go here

That's about it.

Answer 1

It looks to me like each category (cookies, cake, etc) has both forums (like, dislike). Am I reading that right?

If this is true, you should do an OUTER JOIN rather than a LEFT JOIN to produce the comprehensive list, like this:

   ...
        FROM categories
        JOIN forums
    GROUP BY forums.forum_name, forums.forum_desc, forums.forum_id
    ORDER BY categories.cat_id, forums.forum_id

Notice that the ON clause is gone. This means you'll get every forum row joined to every category row.

Notice also that you need to sort by both category and forum if you want the forums to be guaranteed to show up in the same order under each category.

If all forums do not appear for each category, but instead each category has a subset of your forums, the schema you have shown us isn't up to the job. You have a many-to-many relationship between the forums and categories table. To make this work you're going to need another table as follows:

      forums_categories
        cat_id int(8) not null
        forum_id int(8) not null
        PRIMARY KEY (cat_id, forum_id)

This table will need a row for each allowed combination of forum and category. Your from cause becomes:

   ...
        FROM forums_categories fc
        JOIN categories ON (fc.cat_id = categories.cat_id)
        JOIN forums     ON (fc.forum_id = forums.forum_id)
    GROUP BY forums.forum_name, forums.forum_desc, forums.forum_id
    ORDER BY categories.cat_id, forums.forum_id

Hope this helps.