Traversing multi-dimensional arrays

[woe2you]

I've been hacking away at php for a couple of years, but I have no coding background and I'm not particularly good at it. I recently came to rewrite some navigation code for a site I'm doing which uses a database thus:

 

category_id

child_of

category_name

category_description

category_bg

 

to store hierarchical data. I originally had a new query running for every new sublevel of the hierarchy, and I woke up one morning in an inspired mood and figured it would be much easier to work with if I just called the whole thing into one big multidimensional array, thus:

 

function get_categories() // pulls all categories from the database into a two-dimensional associative array for use by other functions
{
$query = "SELECT DISTINCT * FROM categories ORDER BY child_of, category_name";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_assoc($result))
	{
	$categories[$row['child_of']][] = $row;
	}
}
return $categories;
}

 

But now I'm stuck - how can I find an array in this great giant nested mess by its key, when I don't know how far down the hierarchy it is? I want to be able to take any category from the querystring (for example, if someone's hit a landing page somewhere deep in the site) and work backward from that key up the array to the root in order to generate a breadcrumb trail, etc. I don't want to be thrashing the DB all the time, like I used to:

 

function get_breadcrumb() //temporary function to get breadcrumb trail into array
{
if(isset($_GET['category']))
{
$lookup = $_GET['category'];
}
else
{
$lookup = 0;
}
while($lookup > 0)
{
$query = "SELECT * FROM categories where category_id = '$lookup'";
$row = mysql_fetch_assoc(mysql_query($query));
$breadcrumb[$row['category_id']] = $row['category_name'];
$lookup = $row['child_of'];
}
return $breadcrumb;
}

[GingerRobot]

You might benefit from taking a look at this article from the mysql website about storing and working with hierarchical data.

[woe2you]

I tried to look at tree traversal before, but to me it seems even more complicated. With the adjacency list, at least I can understand the data model in my head - which is half the issue.

 

When I was using this code before, I managed to simplify the code I wrote for drop-down menus (nested unordered lists) from needing a query for each list to one query:

 

function draw_menu() // displays top-level product menu
{
$query = "SELECT DISTINCT category_id, category_name, child_of, category_description FROM categories ORDER BY child_of, category_name";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))  
{
// create two-dimensional array of menus
$menus[$row['child_of']][] = $row;
}
draw_ul(0, $menus);
}

function draw_ul($child_of, $menus) // displays nested menus
{
// check if top-level menu
if($child_of == 0)
{
echo "<div class=\"blockshadow\">\n";
}
else
{
echo "\n<div class=\"subshadow\">\n";
}
echo "<ul>\n";
foreach($menus[$child_of] as $key => $value)
{
draw_li($value, $menus);
}
echo "</ul>\n";
echo "</div>\n";
}

function draw_li($item, $menus) // draws each menu item
{
$desc = excerpt_text(strip_tags($item['category_description']), 70);
echo "<li><a href=\"http://" . $_SERVER['HTTP_HOST'] . "/products.php?category=" . $item['category_id'] ."\" title=\"" . $desc . "\">" . $item['category_name'] . "</a>";
// check if item has a submenu
if(isset($menus[$item['category_id']]))
{
draw_ul($item['category_id'], $menus);
}
echo "</li>\n";
}

 

- that took me a lot of effort, and when I'm halfway to understanding it I don't want to throw it all out and start fresh.