organizing some data

[w32]

Hello guys, me again  :D

This time around I'm trying to figure out how to organize some stuff...I've been pulling my hair out for a couple of hours.. (or at least what is left of it)..

Crazy as it may sound, I'm trying to make some very, very simple forums for, umm, shall we say for self-learning purposes.

And I am in a bit of a trouble you see, I have managed to make scripts to create/delete/edit/ categories & forums but I can't seem to be able to order them correctly :( on the forum home

it should be something like

Category1
-Forum1
-Forum2

Category2
-Forum1
-Forum2

I have made this SQL query, but it doesnt work entirely, how could I manage this? I mean pulling out all the categories from a dbase, and then displaying all the forums that correspond to a category id, below that category?

here's my sql :

$category = $db->query('SELECT * FROM forum_categories LEFT JOIN forums ON forum_categories.catid=forums.catid ORDER BY forum_categories.cat_position, forums.position ASC ');

It kinda worked but while it showed all the forums, it repeated categories, should I make separate queries to get the categories and the forums?

[tleisher]

How is your database setup? Last time I made a forum type script I had three tables: users, category, subforum.

Category had an ID, and a name. Subforum had an ID, a name and a parent category ID. So it went something like this:

Category:
id | name
1 | General
2 | Off Topic

Subforum:
id | name | parent
1 | General Forum | 1
2 | Gaming | 1
3 | Asylum | 2

Then something like this:

[code=php:0]
$category = mysql_query("SELECT * FROM `category` ORDER BY `id` ASC") or die(mysql_error());
while ($cat = mysql_fetch_array($category)) {
        $parent = $cat["id"];
        echo $cat["name"]."<br />";
        $subforum = mysql_query("SELECT * FROM `subforum` WHERE `parent` = '$parent' ORDER BY `id` ASC") or die(mysql_error());
        while ($sub = mysql_fetch_array($subforum)) {
                echo "+ " . $sub["name"] . "<br />";
        }
        echo "<br /><br />";
}[/code]

Let me know if that helps.

[w32]

It works somehow like my previous SQL, it brings only the first category, and all of its forums...but hang on I'm thinking on a use of FOR..

[jefkin]

Hi w32,

My opinion is the best way to handle this is with the same query, you used, just add a bit of 'control' logic in your php display.  I'm going to wave my hands
about some of this, since I can't see your display code, but maybe using something like:

[code]
$category = $db->query("SELECT * FROM forum_categories fc LEFT JOIN forums f ON fc.catid=f.catid ORDER BY fc.cat_position, f.position ASC");
$last_cat = -3; // or some other non-used catid number you could always use -2345678 that doesn't seem like a regular category id :)

while ($cat = $db->one_result($category, ...) /* loop on query result row */)  // you fill in your code here.
{
  if ($last_cat != $cat['catid']) // you probably need to change access.
  {
        $last_cat = $cat['catid'];
      ?> <!-- display chunk for category -->
      <div id="category"><?= /* hand waving:  assume your category name is: */ $cat['cat_name'] ?></div>
      <!-- blah blah --><?PHP
    }
    ?><!-- display chunk for forums -->
    <div id="forum"><?= /* more hand waving: assume your forum name is: */ $cat['forum_name'] ?></div>
    <!-- etc, etc, your forum links and stuff... --><?PHP
}
?>
[/code]