Recursive parse MySQL database structure to HTML tree

PHP
1

Hi there,

I’m attempting to parse a structure from MySQL and display in an HTML tree structure using ul’s and li’s. So basically here’s my table structure

CategoryID CategoryParentID CategoryName

  34                    0                    Cat 1
  12                    34                  Cat 2
  45                    34                  Cat 3

So basically the two categories Cat 2 and Cat 3 are sub categories of Cat 1 because they have a parent ID of 34 (the category id of Cat 1) and this goes on and on and on with n number of sub-categories. I’m having lots of trouble writing a recursive function to call and display these in a nice tree.

Can anyone help me? Thanks!

2

Okay so after some trial and error I figured it out. I figured I’d post here for those that are looking for something similar. This may or may not be a good solution but it def. does work! This is used with the Interspire Shopping Cart software to build a tree of all the categories a user has in their store. Here’s the PHP code.


function showCatList($parentid) {
	 $sql = "SELECT categoryid, catparentid, catname FROM isc_categories WHERE catparentid = '$parentid' ORDER BY catsort ASC";
	 
	 $res = mysql_query($sql);
	 
	 $num = mysql_num_rows($res);
	 
	 if ($num > 0) {
	 	echo "<ul id=\\"cat-list\\">\
";
	 }
	 
	 while ($row = mysql_fetch_array($res)) {
	 	
	 	$cntSql = "SELECT count(*) AS cnt FROM isc_categories WHERE catparentid = {$row['categoryid']}";
	 	$cntRes = mysql_query($cntSql);
	 	$cntDat	= mysql_fetch_array($cntRes);
	 	
	 	if ($parentid == 0 || $cntDat['cnt'] > 0) {
	 		echo "<li class=\\"cat-header\\">" . $row['catname'] . "</li>\
";
	 	} else {
	 		echo "<li class=\\"cat-name\\">" . $row['catname'] . "</li>\
";
	 	}
	 	
	 	$v = showCatList($row['categoryid']);
	 }
	 
	 if ($num > 0) {
	 	echo "</ul>\
";
	 }
	 
   	 return true;
}

To call from your script do showCatList(0) because 0 are root category ID’s.