[RESOLVED] Displaying recursive categories

[Horizon88]

Hey all. I know this has been gone over multiple times here, and I'm finally understanding it (I think.) I'm basing all my work off the Sitepoint article on this, and just modifying their sample code 'til I can get it to do what I want, then I'll clean it up. But right now, I have a demo categories table I'm playing with, and this is the function that displays it:

function displayTree($parentID, $level) {
   $result = mysql_query('SELECT `catID`,`catName`,`catParent` FROM categories WHERE catParent="'.$parentID.'";');	
   // display each child
   while ($row = mysql_fetch_array($result)) {
	   // indent and display the title of this child
	   echo str_repeat(' -- ',$level).'<a href="categories.php?id='.$row['catID'].'">'.$row['catName']."</a><br />";

   // call this function again to display this
   // child's children
   displayTree($row['catID'], $level+1);
   }
}

I call it with

displayTree('',0);

and it outputs this:

Blog Posts
-- July Blogs
-- -- 7/22/07 - 7/28/07
-- August Blogs
-- -- First Week of August
Pages
-- HorizonSM Pages
-- -- News and Updates
-- Tutorials

That all works fine, as does calling just a single subcategory. What I'm playing with now is their code to display the path to a node. The function I've modified slightly is here:

function treePath($catID) {
   // look up the parent of this node
   $result = mysql_query('SELECT catParent FROM categories WHERE catID="'.$catID.'";');
   $row = mysql_fetch_array($result);
   // save the path in this array
   $path = array();
   // only continue if this $node isn't the root node
   // (that's the node with no parent)
   if ($row['catParent']!='') {
	   // the last part of the path to $node, is the name
	   // of the parent of $node
	   $path[] = $row['catParent'];
	   // we should add the path to the parent of this node
	   // to the path
	   $path = array_merge(treePath($row['catParent']), $path);
   }
   // return the path
   return $path;
} 

I call that with

print_r(treePath('8'));

and it outputs:

Array
(
    [0] => 0
    [1] => 2
    [2] => 7
)

That's fine too, I understand what it's doing there. What I'd like to know is, can anyone suggest a way I can tie the category name into the path output? I'm thinking I'll need to use a multidimensional array and I'm just looking for some input on what the best way to do this would be. I'm hoping it'll come out to be something I can cycle through with a for loop, and print out the id number and title of the parent category in the path.

Any help?

[sneakyimp]

i haven't tested this and am not sure it will work, but maybe give it a try?

function treePath($catID) {
   // look up the parent of this node
   $result = mysql_query('SELECT c.catParent, c2.catName as catParentName FROM categories c, categories c2 WHERE c2.id=c.catParent AND catID="'.$catID.'";');
   $row = mysql_fetch_array($result);
   // save the path in this array
   $path = array();
   // only continue if this $node isn't the root node
   // (that's the node with no parent)
   if ($row['catParent']!='') {
       // the last part of the path to $node, is the name
       // of the parent of $node
       $path[0] = array();
       $path[0]['id'] = $row['catParent'];
       $path[0]['name'] = $row['catParentName'];
       // we should add the path to the parent of this node
       // to the path
       $path = array_merge(treePath($row['catParent']), $path);
   }
   // return the path
   return $path;
} 

[Horizon88]

Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... line 46.

I put in your code and commented out the function I had... throws a mySQL error?

Edit: I'm logging into my server to check my SQL database. Can you explain what that query does? I've never used anything that looks like that before. :S

[sneakyimp]

the sql is broken. you should be making sure the sql worked before trying to fetch assoc on it. i have tried to fix the sql and my code:

function treePath($catID) {
   // look up the parent of this node
   $result = mysql_query('SELECT c.catParent, c2.catName as catParentName FROM categories c, categories c2 WHERE c2.catID=c.catParent AND c.catID="'.$catID.'";')
      or die('query failed:' . mysql_error());
   $row = mysql_fetch_array($result);
   // save the path in this array
   $path = array();
   // only continue if this $node isn't the root node
   // (that's the node with no parent)
   if ($row['catParent']!='') {
       // the last part of the path to $node, is the name
       // of the parent of $node
       $path[0] = array();
       $path[0]['id'] = $row['catParent'];
       $path[0]['name'] = $row['catParentName'];
       // we should add the path to the parent of this node
       // to the path
       $path = array_merge(treePath($row['catParent']), $path);
   }
   // return the path
   return $path;
} 

[Horizon88]

That works, thanks. 🙂 Can you explain what that SQL query actually does, or give me a link with a bit more information on it? I've never seen the 'c.' c2.' or things like that.

[sneakyimp]

glad it works!

basically i'm using table aliases in the query. you can follow each table name with an alphanumeric identifier which lets you refer to the table by a different name in the select part. aliases are very useful if your table names are super long:

SELECT t1.id, t1.name, t2.id, t2.name
FROM super_long_table_name_that_i_would_never_want_to_type_more_than_once t1,
another_super_duper_really_long_table_name t2 WHERE
t2.id=t1.fkey

they are also useful for joining a table to itself as i did in your query. if you are joining a table to itself, you need a way to distinguish which record's field you are referring to when you ask for something like catName. if you don't use table aliases, then you can't really join a table to itself at all as far as i know. just try it.

[Horizon88]

Cool. I'm going to read up some more on SQL queries tonight, I think. 🙂

That code works just how I needed it to, thanks!