Building a tree using nested unordered lists

Question

How can I build this HTML code:

<ul class="tree">
    <li>Animals
        <ul>
            <li>Birds</li>
            <li>Mammals
                <ul>
                    <li>Elephant</li>
                    <li>Mouse</li>
                </ul>
            </li>
            <li>Reptiles</li>
        </ul>
    </li>
    <li>Plants
        <ul>
            <li>Flowers
                <ul>
                    <li>Rose</li>
                    <li>Tulip</li>
                </ul>
            </li>
            <li>Trees</li>
        </ul>
    </li>
</ul>

From this structure:

CREATE TABLE `categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `position` INT(11) DEFAULT NULL,
  `parent_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id_fk` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories` (`id`, `position`, `parent_id`)
VALUES
    (1,1,NULL),
    (2,2,NULL),
    (3,1,1),
    (4,2,1),
    (5,1,4),
    (6,2,4),
    (7,3,1),
    (8,1,2),
    (9,1,8),
    (10,2,8),
    (11,2,2);

CREATE TABLE `categories_locale` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(100) NOT NULL DEFAULT '',
  `name` VARCHAR(40) NOT NULL DEFAULT '',
  `path_cache` text,
  `category_id` INT(11) NOT NULL,
  `locale_id` SMALLINT(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories_locale` (`id`, `slug`, `name`, `path_cache`, `category_id`, `locale_id`)
VALUES
    (1,'animals','Animals',NULL,1,1),
    (2,'plants','Plants',NULL,2,1),
    (3,'birds','Birds',NULL,3,1),
    (4,'mammals','Mammals',NULL,4,1),
    (5,'elephant','Elephant',NULL,5,1),
    (6,'mouse','Mouse',NULL,6,1),
    (7,'reptiles','Reptiles',NULL,7,1),
    (8,'flowers','Flowers',NULL,8,1),
    (9,'rose','Rose',NULL,9,1),
    (10,'tulip','Tulip',NULL,10,1),
    (11,'trees','Trees',NULL,11,1);

Not much. I'm stuck after building the SQL and HTML. I have no idea how to do it. — oaziz, Jan 07 '12 at 07:06
This might help you get started http://stackoverflow.com/questions/5291054/hierarchical-sql-problem/5291159#5291159 — Jon Black, Jan 07 '12 at 11:23

score 2 · Accepted Answer · answered Jan 07 '12 at 17:52

Hmm, I believe there must be examples available online on how you can accomplish this. Some of them might even talk about new ways to store hierarchical data and you'll find the readings interesting.

Anyways, this code snippet, based on recursion, might help you achieve your HTML.

<?php
// recursive function to generate the category HTML
function generateTree ($parent) {
    global $arrPCat, $arrCat;
    if (array_key_exists($parent, $arrPCat)) {
        echo '<ul' . ($parent == 0 ? ' class="tree"' : '') . '>';
        foreach ($arrPCat[$parent] as $arrC) {
            echo '<li>' . $arrC['name'] . '</li>';
            generateTree($arrC['id']);
        }
        echo '</ul>';
    }
}

// read all categories from the DB
$rs = mysql_query('SELECT  `cl`.`id`, `cl`.`name`, `c`.`position`, IFNULL(`c`.`parent_id`, 0) AS `parent_id`
    FROM  `categories_locale`  `cl`
    LEFT JOIN  `categories`  `c` ON  `cl`.`id` =  `c`.`id`
    ORDER BY  `c`.`parent_id` ,  `c`.`position`');
while ($r = mysql_fetch_assoc($rs)) {
    // store parent and its children into the $arrPCat Array
    $arrPCat[$r['parent_id']][] = Array (
                                    'id' => $r['id'],
                                    'name' => $r['name']
                                  );
}
generateTree (0); // now generate the HTML for the category tree
?>

Hope it helps!

This is a fairly poor way to build the tree as it requires multiple SQL queries. A better option would be to retrieve all the categories in 1 query and the build the tree using PHP array functions. The above code could be improved by caching the final HTML and reading this from a temp file. — Alexander Holsgrove, Oct 09 '12 at 10:59
@AlexHolsgrove, the above code executes a single query and not multiple queries. Not sure if I understood you correctly — Abhay, Oct 16 '12 at 16:25
Ignore everything I said in my previous comment! I misread a } and thought that the function was recursive and called a new query each time. (Note to self: read more carefully) — Alexander Holsgrove, Oct 17 '12 at 13:14
Haha, that's not a problem at all @AlexHolsgrove. I understand the code itself isn't very well indented making it difficult to read :-) — Abhay, Oct 31 '12 at 07:12

score 0 · Answer 2 · answered Dec 15 '14 at 18:34

There is a slight error I come across with Abhay's answer. His solution does not make the UL nested within the LIs, atleast when I implement his solution. However, its a quick fix. You simply need to echo the closing li tag after the recursive call is made. This allows every list item to be fully created, even if it does not have any sub-items, before it is ended.

Abhay's code:

foreach ($arrPCat[$parent] as $arrC) {
        echo '<li>' . $arrC['name'] . '</li>';
        generateTree($arrC['id']);
    }

The correction:

foreach ($arrPCat[$parent] as $arrC) {
        echo '<li>' . $arrC['name'] ;
        generateTree($arrC['id']);
        echo '</li>';
    }

score 0 · Answer 3 · answered Jan 07 '12 at 06:28

0

Try constructing the actual tree in PHP, then using depth-first search to generate the HTML.

answered Jan 07 '12 at 06:28

Steve Wang

1,814
1
16
12

Building a tree using nested unordered lists

3 Answers3

Linked