Build category - sub category html list from mysql table

Question

Hello,

i have a table with categoryId, name and parentId. I would like to build a html list with parent and child tree like structure

my table looks like this

------------------------------------------------------------------
id  categoryId  parentId  categoryName      SortSeq
------------------------------------------------------------------
1  438044691  NULL        test              1   
2  438044692  438044691   test item one     2   
3  438044693  438044691   test item two     3
1  438044701  NULL        testOne           4    
2  438044702  438044701   testOne item one  5   
3  438044703  438044701   testOne item two  6
1  438044709  NULL        testTwo           7   
2  438044710  438044709   testTwo item one  8   
3  438044711  438044709   testTwo item two  9

---

the structure can have sub-sub category item as well. but its just one in this example.

i'll be more than happy to give you more information if you think the question is incomplete.

thank you very much.

Answer 1

You're probably looking at a recursive function as being the simplest solution to this. A really simple example is something like this, there are various techniques you could use to make this a proper tree structure. Really depends what you want to do tbh

function showlist($parent=NULL) {
    $q = ($parent !== NULL) ? "SELECT `id`,`name` FROM `categories` WHERE `parentId` ='".$parent."'" : "SELECT `id`,`name` FROM `categories` WHERE `parentId` IS NULL";
    $result = mysql_query($q)or die(mysql_error()); 
    while ($cat = mysql_fetch_assoc($result)) {             
        echo $line['name'];
        showlist($line['id']); 
    }
}

Theres some really good info on the MySQL site about managing hierarchical data but the most elegant solutions would mean you changing your Schema which i'm not sure if you're able to do or not?

Answer 2

I use "Modified Preorder Tree Traversal", which is also called "Nested Sets". This Sitepoint article provides a good summary of how it works. Hector Virgen has written a Zend_Db_Table enhancement to support it that I am using because my app is built on the Zend Framework. I've also used the code written by Nick Pack to build the tree in PHP.

Answer 3

look at this article

Answer 4

function categorylist($parent=NULL, $level = 0) {
    $sql = "SELECT `id`,`name` FROM `categories` WHERE `parentId`";
    if($parent === NULL) {
        $sql .= " IS NULL";
    else {
        $sql .= " = '".$parent."'";
    }
    $sql .= " ORDER BY `SortSeq`";
    $res = mysql_query($sql); 
    while ($cat = mysql_fetch_object($res)) {           
        echo '<li class="level'.$level.'">'.$line['name'].'</li>';
        $new_level = $level + 1;
        categorylist($line['id'], $new_level); 
    }
}

echo '<ul>';
categorylist();
echo '</ul>';

You can then style the list how you want with each each class level1, level2 etc..