[SOLVED] LEFT JOIN problem with my categories/forums

[Aureole]

Ok say there is a category called "Test Category" and it has two children forums. I want it to show like this:

 

Test Category

 

Forum 1

 

Forum 2

 

But it is showing like this:

 

Test Category

 

Forum 1

 

Test Category

 

Forum 2

 

My query is like...:

 

<?php
$query ="SELECT * FROM forums LEFT JOIN categories ON forums.forum_parent_id=categories.cat_id";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
    $cid = $row['cat_id'];
$cname = $row['cat_name'];
$fname = $row['forum_name'];
$fdesc = $row['forum_description'];
// .... etc...
?>

 

To see exactly what I mean go here.

 

Anyone know how I can fix this?

 

[Barand]

try

<?php

$query ="SELECT * FROM forums 
        LEFT JOIN categories 
            ON forums.forum_parent_id=categories.cat_id
        ORDER BY categories.cat_id, forums.forum_name";
$result = mysql_query($query) or die(mysql_error());

$prevCat = '';
while($row = mysql_fetch_assoc($result)) {
    $cid = $row['cat_id'];
$cname = $row['cat_name'];
$fname = $row['forum_name'];
$fdesc = $row['forum_description'];
    /**
    * is it a new category
    */
    if ($prevCat != $cid)
    {
        echo "<h3>$cname</h3>";
        $prevCat = $cid;
    }
    echo "<p>$fname<br>$fdesc</p>";
?>

[Aureole]

Thanks a lot, that works a charm... now to look at it and try learn how you made it work.

 

Thanks again... this has been bugging me for Hours.

[Aureole]

Ok that fixed my problem but created another one and it's hard to explain...

 

If you go here you'll see that it worked but you'll also that the second forum has the border around it etc. and if you look at the source it is in it's own table basically it needs to be within the table before it...I've tried playing around and expanding on what you gave me but I can't get it to work right...

 

Here's the full code if that'll make things easier... it's not too  big...

 

<?php
session_start();

include('functions.php');

dbConnect();

if (userLogged()) {
  updateWhere($_SESSION['mem_id'], 'viewing the Forum'); }

$pagetitle = 'Forum';
$navcurrent = 'Forum';
$prevCat = '';
$prevTitle = '';

include('inc/header.php');
?>

    <div id="inner">

<?php
$query ="SELECT * FROM forums LEFT JOIN categories ON forums.forum_parent_id=categories.cat_id";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
    $cid = $row['cat_id'];
$cname = $row['cat_name'];
$fname = $row['forum_name'];
$fdesc = $row['forum_description'];
?>

<div class="con_forum_outer">

<?php
    if ($prevCat != $cid) {
?>

            <div class="con_forum_title"><h1><?php echo $cname; ?></h1></div>

<?php
    $prevCat = $cid;
    }
?>

            <div class="con_forum_inner">

                <table width="100%" cellspacing="1" style="background-color:#000000;">

<?php
    if ($prevTitle != $cid) {
?>
                    <tr>
                        <th width="50%" class="forum_th">Forum</th>
                        <th width="10%" class="forum_th">Topics</th>
                        <th width="10%" class="forum_th">Replies</th>
                        <th width="30%" class="forum_th">Last Post</th>
                    </tr>

<?php
    $prevTitle = $cid;
    }
?>

                    <tr>
                        <td width="50%" style="background-color:#242424;padding:5px;"><p class="content"><strong><?php echo $fname; ?></strong><br /><?php echo $fdesc; ?></p></td>
                        <td width="10%" style="text-align:center;background-color:#242424;"><p class="content"></p></td>
                        <td width="10%" style="text-align:center;background-color:#242424;"><p class="content"></p></td>
                        <td width="30%" style="background-color:#242424;padding:5px;"><p class="content"></p></td>
                    </tr>

                </table>

            </div>

        </div>

<?php
}
?>
    </div>
<?php
include('inc/footer.php');
?>

[Aureole]

I've got it all working so I'll set this as solved.