Need some advice....

[AdamPGT]

So I'm building a content/document management syste and it's turned a bit more complicated than I had anticipated. The problem I've run into is how I should setup my DB tables. Basically, I would like some outside input on DB structure so I can get my navigation to work like it should.

The navigation has a Parent Category, with sub-categories and pages. But each sub category should have the ability to have subs and pages, etc. Now some of these sub-categories will be uploaded PDF docs.

The tricky part of the navigation, or at least the part I'm having a bit of trouble with is that I only want the "active" category to be expanded. Kind of like this:

-- No Active sub-cat
Parent Category
---> Sub Category 1
---> Sub Category 2
---> Sub Category 3

-- Active sub-cat
Parent Category
---> Sub Category 1
---> Sub Category 2
-------> Sub-Sub Category 1
-------> Sub-Sub Category 2
-------> Sub-Sub Page 1
---> Sub Category 3

-- Active sub-sub-cat
Parent Category
---> Sub Category 1
---> Sub Category 2
-------> Sub-Sub Category 1
----------> Sub-Sub-Sub Category 1
----------> Sub-Sub-Sub Category 2
-------> Sub-Sub Category 2
-------> Sub-Sub Page 1
---> Sub Category 3

And so on and so on. Any DB setup suggestions? I don't need any code help or anything, just figured someone would have a suggestion that would give me a push in the right direction.

--Adam

[Barand]

Try something like this

categories table
[code]ID | parentID | catname | [/code]

Top-level categories have parentID = 0.


[code]session_start();
include 'db.php';
         // set up the array if it isn't there
       if (!isset($_SESSION['cats'])) {
           $res = mysql_query( "SELECT ID, catname, parentID FROM categories");
           while (list($i, $n, $p) = mysql_fetch_row($res)) {
                  $_SESSION['cats'][$i] = array('parent'=>$p,  'name'=>$n, 'active'=>0);
           }
       }


       if (isset($_GET['selected'])) {
           clearActive();
           setActive($_GET['selected']);
       }

       displayCategory(0);

//-------- supporting functions ---------------------------------
function clearActive() {
         foreach($_SESSION['cats'] as $kid => $karray) {
                 $_SESSION['cats'][$kid]['active'] = 0;
         }
}

function setActive ($i) {
         foreach($_SESSION['cats'] as $kid => $karray) {
                 if ($kid == $i) {
                     $_SESSION['cats'][$kid]['active'] = 1;
                       // set parents active also
                     if ($karray['parent'] != 0)
                         setActive($karray['parent']);
                 }
         }

}

function displayCategory($id, $level=0) {
         foreach($_SESSION['cats'] as $kid => $karray) {
                 if ($karray['parent'] == $id) {
                     $p = 10*$level;
                     echo "<span style='padding-left: {$p}px' onClick='location.href=\"?selected=$kid\"'>"
                            . $karray['name'] . '</span><br>';
                     if ($karray['active'])
                         displayCategory($kid, $level+1);
                 }
         }

}[/code]