Show Categories & Sub-Categories in a tree format

This post will explain how to show categories and sub-categories in a tree format in a directory.

Example:
We need to create a table called `categories` and the table Fields:
" id ", this should be a int(11), primary, and auto-increment.
" name ", this should be varchar(255) [ Name of the category ]
" parentid ", this should be int(11), [ " id " of the Parent category, if this is a sub-category, If  " parentid " is 0, that means the category is a top-level category ]

Code for display Categories Dropdown:
$connection = mysql_connect('localhost', 'root', '');
if (!$connection) {
   die('Could not connect: ' . mysql_error());
}

$db = mysql_select_db ('test1', $connection);
$sql = "SELECT * from categories order by name ASC";
$cats = mysql_query($sql) or die("Could not select category");

echo "<select name="category">";
recall (0,0,$cats);
echo "</select><br>";

Function recall() is the actual function that is going to generate the tree structure for our categories and sub-categories. We initially pass it values of 0,0,and $cats for its root, depth, and the actual query data. We pass is 0 and 0 for root and depth because initially we want to start from top level directories.



Now the recall function:

function recall ( $root, $depth, $sql ) {
   $row = 0;
   while ( $acat = mysql_fetch_array ($sql) ) {
      if ( $acat['parentid'] == $root ) {
          echo "<option value='" . $acat['id'] . "'>";
          $j = 0;
       while ( $j < $depth ) {
            echo "-";
            $j++;
        }
        if ( $depth > 0 ) {
           echo "-";
        }
       echo $acat['name'] . "</option>";
        mysql_data_seek ( $sql,0 );
        recall ( $acat['id'], $depth+1, $sql );
    }
   $row++;
   mysql_data_seek ($sql, $row);
   }
}

The mysql_data_seek function is to rebuffer the query. So that you can go through it again at the end of each iteration.

It looks bit complicated but basically its going through all the categories in the while loop and seeing if the current categories is equal to root. Since initially the root is zero, it just prints the category without indentations. It also sets a variable, j, to keep track of how deep in the tree a category is, so it can print the appropriate number of indentations (-) segments so the tree will look right. Also if the depth of the categories is not zero, that means its a sub-category, it will print a dash indicating its a sub-category of a higher level category.

Then the function calls itself and adds one to the depth only if the " parentid " is not zero. This way, it will keep transversing the nodes of the tree until it goes through all the categories and subcategories. Calling itself and incrementing the depth by 1 ensures proper level of the category within the tree. When the while loop finishes, you will have all the categories displayed in tree format.