Page 1 of 1

displaying tree structure of a database table

Posted: Wed Nov 08, 2006 5:45 am
by sh33p1985
i have a table in my database for product categories:

catID | catName | subcatOf

consider the following entries:

1 | CLOTHING | NULL
2 | CLOTHING-KIDS | CLOTHING
3 | CLOTHING-KIDS-TSHIRTS | CLOTHING
4 | CLOTHING-MENS | CLOTHING
5 | CLOTHING-WOMENS | CLOTHING
6 | TECHNOLOGY | NULL

for the sake of adding a product i want to be able to select its category from a drop down menu is a display of this format:

CLOTHING
-KIDS
--TSHIRTS
-MENS
-WOMENS
TECHNOLOGY

so you can see the heirarchical structure of the category tree...

im having real problems getting the desired output tho, at the moment i can get it to output

CLOTHING
KIDS
TSHIRTS
MENS
WOMENS
TECHNOLOGY

but im having difficulties appending the "-" to denote what level the category is in the tree

this is my current code:

Code: Select all

echo "<select name=\"productCategory\" id=\"productCategory\">";
echo "<option value=\"null\">Please Select...</option>";
displayCategories("NULL", 0);
echo "</select>";

function displayCategories($rootCat, $level){
  $connection = mysql_connect("localhost", "root", "") or die (mysql_error());
  mysql_select_db("individ-jewels", $connection) or die (mysql_error());
  $sql = "SELECT * FROM tbl_category WHERE subcategoryOf='$rootCat'";
  $mysql_result = mysql_query($sql, $connection) or die (mysql_error());
  $num_rows = mysql_num_rows($mysql_result); 
    if($num_rows <> 0){//subcatergory exists
      while($row = mysql_fetch_assoc($mysql_result)){
      $newRootCat = $row["categoryName"];
      echo "<option value=\"$newRootCat\">" . $newRootCat . "</option>";
      $level++;
      displayCategories($newRootCat, $level);
    } 								
  }
  else{//no subcategory go back a lvl
  //do stuff here to go up a level							
}
}

Posted: Wed Nov 08, 2006 6:44 am
by Mordred
This article may help clarify some things: http://www.sitepoint.com/article/hierar ... a-database

Code: Select all

1 | CLOTHING | NULL 
2 | CLOTHING-KIDS | CLOTHING
make it like this:

Code: Select all

1 | CLOTHING | NULL 
2 | CLOTHING-KIDS | 1