Need help with recursive function php mysql

Hello everyone!

I have a mysql table and these fields:
category_id, category_name, parent_id

What I am trying to make is a tree menu with categories and subcategories. I need some help on expanding subcategories depending on how deep in the tree i go. I have this function which list all categories and subcategories with indent.

[code]
<?php
function listCategory($parent = 0, $level = 0)
{
  // where parent of 0 means it's top-level
  $retval = "";
  $indent = "<br />";
  for ($i=0; $i<$level; $i++) $indent .= "&nbsp; &nbsp; &nbsp;";
   
   
  $result = mysql_query("SELECT * FROM categories WHERE parent_id='$parent'");
  while ($row = mysql_fetch_assoc($result))
  {
        extract($row);
        $retval .= $indent . "<a href=\"?c=$category_id\">" . $category_name . "</a>";
        $retval .= listCategory($category_id, intval($level) + 1);
  }
  return $retval;
}
?>
[/code]

What I would like to do is to expend the subcategories after I click on a top category and getting the qyerystring $_GET['c']
Anyone can help me with this or knows any better solution?
Thanks in advance

i imagine it would be easier to load your db info into an array, and process the array as necessary rather than lopping your results all into a string.  then you can specify how deep in the array itself to go (ie. top-level, second, etc.), and stop where appropriate.

A single query plus array alternative
[code]
<?php

$sql = "SELECT c.category_id, c.parent, c.name FROM categories c";
$res = mysql_query($sql) or die(mysql_error());
$cats = array();
while (list($id,$parent,$cat)=mysql_fetch_row($res)) {
    $cats[$parent][$id] = $cat;
}


function subcats(&$cats, $pid, $level=0)  {
    if (isset($cats[$pid])) {
        foreach ($cats[$pid] as $id=>$cat) {
            $indent = '<br/>'.str_repeat('&nbsp', 3*$level);
            echo "$indent$cat";
            subcats($cats,$id, $level+1);
        }
    }

}

subcats ($cats,0);
?>
[/code]

This one looks very nice. One question though. How can I show only top categories and get the subs only when i get a querystring in the address bar

try this variation

[code]
<?php
include '../test/db.php';

$sql = "SELECT c.category_id, c.parent, c.name FROM categories c";
$res = mysql_query($sql) or die(mysql_error());
$cats = $maincats = array();
while (list($id,$parent,$cat)=mysql_fetch_row($res)) {
    $cats[$parent][$id] = $cat;
    if ($parent==0) {
        $maincats[$id] = $cat;
    }
}

foreach ($maincats as $id => $cat) {
        echo "<a style='text-decoration:none' href='?id=$id'>$cat</a><br/>";
        if (isset($_GET['id']) && $id == $_GET['id']) {
            subcats ($cats,$id);
        }
}

function subcats(&$cats, $pid, $level=1)  {
    if (isset($cats[$pid])) {
        foreach ($cats[$pid] as $id=>$cat) {
            $indent = str_repeat('&nbsp', 3*$level);
            echo "$indent$cat<br/>";
            subcats($cats,$id, $level+1);
        }
    }

}


?>[/code]

This works good. But whan i click on a sub category then the tree menu collapses. It suposed to stay expanded when it shows the id of a subcategory. If u know what i mean

Plan C
[code]
<?php

$sql = "SELECT c.category_id, c.parent, c.name FROM categories c";
$res = mysql_query($sql) or die(mysql_error());
$cats = $index = $ancestors = array();
while (list($id,$parent,$cat)=mysql_fetch_row($res)) {
    $cats[$parent][$id] = $cat;
    $index[$id] = $parent;
}


if (isset($_GET['id'])) {
    $i = $_GET['id'];
    $ancestors[] = $i;
    do  {
        $ancestors[] = $index[$i];
        $i = $index[$i];
    } while ($index[$i] != 0);
}

subcats($ancestors, $cats, 0);

function subcats(&$ancestors, &$cats, $pid, $level=0)  {
    if (isset($cats[$pid])) {
        foreach ($cats[$pid] as $id=>$cat) {
            $indent = str_repeat('&nbsp', 3*$level);
            echo "$indent<a  href='?id=$id'>$cat</a><br/>";
            if (in_array($id, $ancestors))
                subcats($ancestors, $cats, $id, $level+1);
        }
    }

}

?>[/code]

This one is awsome. I get an error when i click on categories which have parent 0 (top level)

[b]Notice: Undefined index: 0 [/b]

this line
[code]
do  {
        $ancestors[] = $index[$i];
        $i = $index[$i];
    } while ($index[$i] != 0); // Undefined index: 0
[/code]

Change

    } while ($index[$i] != 0);

to

    } while ($i != 0);

Thanks man, that worked like a charm you are the best.
I have also an other task that I need to do.

I have another field in the database which is: category_status, 1 = active and 0 = inactive. All I want to do is to run an update to the mysql table. Now when I update the top level category_status to 0, then I want to deactivate all the other subcategories under that top category. You think you can help me with that

try
[code]
<?php

$sql = "SELECT c.category_id, c.parent, c.name, c.category_status FROM categories c";
$res = mysql_query($sql) or die(mysql_error());
$cats = $index = $ancestors = $status = array();
while (list($id,$parent,$cat, $stat)=mysql_fetch_row($res)) {
    $cats[$parent][$id] = $cat;
    $index[$id] = $parent;
    $status[$id] = $stat;
}


if (isset($_GET['id'])) {
    $i = $_GET['id'];
    if ($status[$i]) $ancestors[] = $i;
    do  {
        $ancestors[] = $index[$i];
        $i = $index[$i];
    } while ($i != 0);
}

subcats($ancestors, $cats, 0);

function subcats(&$ancestors, &$cats, $pid, $level=0)  {
    if (isset($cats[$pid])) {
        foreach ($cats[$pid] as $id=>$cat) {
            $indent = str_repeat('&nbsp', 3*$level);
            echo "$indent<a  href='?id=$id'>$cat</a><br/>";
            if (in_array($id, $ancestors))
                subcats($ancestors, $cats, $id, $level+1);
        }
    }

}

?>[/code]

How and where do I run the update sql query?

[code]
$sql = "UPDATE categories SET etc. etc.";
[/code]

I would have thought an admin function such as setting status of a category would be in a separate admin script.