fold out categories hierarchy from DB

hi!

i'm stuck with hierarchial fold out data display taken from database.
say i've got main menu elements:

home
audio
foo


if one category is pressed and it has children, these children should appear.
audio is accessed:

home
audiospeakers
headphonesfoo

speakers is accessed:

home
audiospeakers2.1
4.1headphones
foo

foo is accessed and audio->speakers are closed:
home
audio

foo
something

my db layout:

+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| cat_id | int(10) unsigned | | PRI | NULL | auto_increment |
| parent_cat_id | int(100) | YES | MUL | NULL | |
| cat_name | varchar(150) | | UNI | | |
| cat_order | int(11) | | | 0 | |
+---------------+------------------+------+-----+---------+----------------+


finally, my php code:

function select( $level = 0, $pre = '' )
{
$SQL = 'SELECT
*
FROM
' . CATEGORIES_TABLE . '
WHERE
parent_cat_id = ' . $this->parent . ' ORDER BY cat_id';

$result = mysql_query( $SQL ) or die( mysql_errno() );

$i = 0;

while( $row = mysql_fetch_assoc( $result ) )
{

$this->cats .= str_repeat( '&nbsp;&nbsp;', $level ) . "<a href=\"" . $_SERVER['PHP_SELF'] . "?cat_id="
. $row['cat_id'] . "\">"
. $row['cat_name'] . "</a><br>" . "\n";

$this->parent = $row['cat_id'];

if( $_REQUEST['cat_id'] == $row['cat_id'] )
{
$this->select( $level + 1, $pre = $row['parent_cat_id'] );
}

//before i fetched the entire structure
//$this->select( $level + 1, $pre = $row['parent_cat_id'] );

$i++;
}
}

initially, it displays all the main level cats. then, if any category is hit and one's got children they're displayed too, this condition is responsible of it: 'if( $_REQUEST['cat_id'] == $row['cat_id'] )'. but if this child cat is accessed, condition is evaluated to false b/c $_REQUEST['cat_id'] != $row['cat_id'] and it doesn't fold out anylonger. any ideas on how to implement such behaviour?

thanx!

I'm not too sure about the subcat thing, but marek posted this interesting link about hierarchal tree categories or something like that which might help.

MPTT (http://www.philbrodeur.com/tutorials/mptt/)

do while loops in while loops ;)

bah

I couldn't figure out how to do this either so if you ever find a solution let me know.

You need to recursively check for parent_cat_id until it equals your top level...

I still don't understand the 'right id / left id' method enough to incorporate it anywhere *sigh*

I'm not too sure about the subcat thing, but marek posted this interesting link about hierarchal tree categories or something like that which might help.

MPTT (http://www.philbrodeur.com/tutorials/mptt/)

thanx for the link, but i'm using adjacency list model, not preorder traversal algorythm.

Just re-reading and looks like I am looking for the same thing.

e.g.
a top level menu comprised of:

Public
Private
Old

choosing any one of them then changes the menu to something like:

Public
Private
-Parts
-Service
Old

though obviously we have to consider:
Public
Private
-Parts
--Meat
--Veg
-Service
Old

etc.

Although this tutorial (http://www.sitepoint.com/article/hierarchical-data-database) has been useful, I still can't see how to adapt it for my needs. It appears that it can show the entire tree, or just a portion of it, but not what I want!. Maybe I'm missing something?

romalong,
http://www.chipchapin.com/WebTools/MenuTools/
might have something useful, working my way through at the moment

I'd suggest adding a field that would store the path to the subcategory (just name & id of all it's parents).
It's the simplest way to have the path "cached" so that you can build the links from it.
Generating the field is simple. If it's empty it copies it's parents path and adds it's parents data (recursibve if the parent's data is empty aswell).