Generate Nested UL's Based Upon Variable Depth Data

Question

I have some hierarchical data that I need to display in a series of nested UL's. For each item I have a name, an ID, and a depth value. Normally I would just group these items by depth, but I actually need to create a tree structure with my data, like this: My sample data in MySQL Workbench

Here is my question: is there a good way to generate valid markup (I would love it if I could make it print out with proper tabbing too, but that will be tough) where my data will be wrapped in nested UL's? I already have a solution that kinda works, but I am getting a single stray tag. Here is the code I have for that:

<?php
    include("includes/classes/Database.class.php");
    $db = new Database();
    $query = "SELECT COUNT(parent.Name) - 2 as level, node.Name AS Name, node.ID
    FROM Region AS node, Region AS parent
        WHERE node.LeftVal BETWEEN parent.LeftVal AND parent.RightVal and node.Name <> 'Earth'
            GROUP BY node.ID
            ORDER BY node.LeftVal";
    $results = $db->executeQuery($query);
?>
<!DOCTYPE HTML>
<html lang="en-US">
<head>
    <meta charset="UTF-8">
    <title></title>
</head>
<body>
    <?php
        $last_level = 0;
    ?>
    <ul id="regionTree">
    <?php
        while ($row = mysql_fetch_assoc($results)) {
            $link = '<li>'.PHP_EOL.'<a href="addChild.php?parentid='.$row["ID"].'">'.$row["Name"]."</a>".PHP_EOL;
            $diff = $last_level - $row["level"];
            if($diff == 0){
                // Sibling
                echo ($row["level"] != 0) ? '</li>'.PHP_EOL.$link:$link;
            }
            elseif($diff < 0){
                // Child
                $demoter = '<ul>'.PHP_EOL;
                for ($i=0; $i > $diff; $i--) { 
                    echo $demoter;
                }
                echo $link;
            }
            else{
                // Parent
                $promoter = '</li>'.PHP_EOL.'</ul>';
                for ($i=0; $i < $diff; $i++) { 
                    echo ($row["level"] != 0) ? $promoter.PHP_EOL."</li>":$promoter;
                }
                echo $link;
            }

            $last_level = $row["level"];
        }
    ?>
    </li>
    </ul>
</body>
</html>

Any Ideas?

::Edit:: I have created a pastebin with the generated source which does not validate. Pastebin.com

::EDIT 2:: Here is the schema for the Region table. It is designed using a hybrid of the nested set model and the adjacency list model.

CREATE TABLE Region (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Stores the ID for the Region.',
    Name VARCHAR(45) NOT NULL COMMENT 'Stores the name of the Region',
    Region_Type VARCHAR(45) NOT NULL COMMENT 'Stores the Region type.',
    Parent INT COMMENT 'Stores the ID of the Parent Region',
    LeftVal INT NOT NULL,
    RightVal INT NOT NULL,
PRIMARY KEY (ID)
) COMMENT 'Stores information about all Regions.' ENGINE=INNODB
ROW_FORMAT=DEFAULT CHARACTER SET utf8 collate utf8_general_ci;

Can you post your database schema for the `Region` table? – Keyne Viana Feb 28 '12 at 15:01 — Keyne Viana, Feb 28 '12 at 15:01
I have added that schema for you. – Brendon Dugan Feb 28 '12 at 15:49 — Brendon Dugan, Feb 28 '12 at 15:49
Okay, I've edited my answer with your schema. – Keyne Viana Feb 28 '12 at 17:22 — Keyne Viana, Feb 28 '12 at 17:22

score 8 · Accepted Answer · edited May 23 '17 at 10:32

8

This should work:

$query = "SELECT node.Name, (COUNT( parent.Name ) -1) AS depth FROM region AS node
            CROSS JOIN region AS parent
                WHERE node.LeftVal BETWEEN parent.LeftVal
                    AND parent.RightVal
            GROUP BY node.Name
            ORDER BY node.LeftVal";

$result = mysql_query($query);

// Build array
$tree = array();
while ($row = mysql_fetch_assoc($result)) {
    $tree[] = $row;
}

// Bootstrap loop
$result        = '';
$currDepth     = 0; 
$lastNodeIndex = count($tree) - 1;
// Start the loop
foreach ($tree as $index => $currNode) {
    // Level down? (or the first)
    if ($currNode['depth'] > $currDepth || $index == 0) {
        $result .= '<ul>';
    }
    // Level up?
    if ($currNode['depth'] < $currDepth) {
        $result .= str_repeat('</ul></li>', $currDepth - $currNode['depth']);
    }
    // Always open a node
    $t = ($index == 0) ? 1 : 2;
    $result .= '<li>' . $currNode['Name'];
    // Check if there's chidren
    if ($index != $lastNodeIndex && $tree[$index + 1]['depth'] <= $tree[$index]['depth']) {
        $result .= '</li>'; // If not, close the <li>
    }
    // Adjust current depth
    $currDepth = $currNode['depth'];
    // Are we finished?
    if ($index == $lastNodeIndex) {
        $result .= '</ul>' . str_repeat('</li></ul>', $currDepth);
    }
}

// Indent the code
// For UTF8: tidy_parse_string($result, array('indent' => true, 'show-body-only' => true), 'UTF8')
$result = tidy_parse_string($result, array('indent' => true, 'show-body-only' => true));
print $result;

Getting a modified preorder tree traversal model (nested set) into a <ul>

How to generate a tree view from this result set based on Tree Traversal Algorithm?

How to create an array from this result set (nested categories stored in databased with traversal model)?

edited May 23 '17 at 10:32

Community

1
1

answered Feb 28 '12 at 14:59

Keyne Viana

6,194
2
24
55

The output of this looks really nice, but unfortunately it does not generate valid markup. – Brendon Dugan Feb 28 '12 at 19:12
1

If you want a sexy code, you could also work on the indentation =D – Keyne Viana Feb 28 '12 at 21:20
Super! I won't be able to award the bounty for another 16 hours, but You've earned it! – Brendon Dugan Feb 28 '12 at 21:27
Any idea how one would go about indenting it? Even if it was imperfect, some indentation would go a long way towards making me happy. I'll bet we could do it with the depth variable, but I'm not entirely certain how. – Brendon Dugan Feb 28 '12 at 21:41
1

I've added the indentation using http://www.php.net/manual/en/tidy.parsestring.php – Keyne Viana Feb 28 '12 at 22:37
1

Also removed the PHP_EOL since the tidy parser already add it. – Keyne Viana Feb 28 '12 at 22:54
That is wonderful! On top of the bounty I also upvoted all of your comments for some extra rep! – Brendon Dugan Feb 29 '12 at 00:20
1

Oh... yeah... I knew that. I was just... making sure you were paying attention! Yeah, that's it... – Brendon Dugan Feb 29 '12 at 13:40

Your Common Sense · Answer 2 · 2012-02-28T15:13:34.487

Some time ago I come across the similar problem - a template to output a correct HTML tree.
Unfortunately, at the time I have at hand only a template itself, not the code preparing an array with data:

<? foreach ($TREE as $row): ?> 
<?   if($row['li']=="open"): ?>
<ul>
<?   endif ?> 
<?   if($row['li'] == "close"): ?>
</ul>
<?   endif ?> 
<?   if($row['id']): ?> 
<?     if($id == $row['id']): ?> 
  <li><?=$row['title']?></li> 
<?     else: ?> 
  <li><a href="?id=<?=$row['id']?>"><?=$row['title']?></a></li> 
<?     endif ?> 
<?   endif ?> 
<? endforeach ?>

I'll try to find the code and post it here. Though it is not too complex. The main idea is to "unfold" a recursive tree to the plain list (with some "service" rows added contains no data to display but only tag markers ).

I wouldn't call it too nice solution but that was the best result I was able to get trying to separate business logic from display logic as much as possible.

The code can be simplified though as at the moment it supports link highlighting (it was intended to output site structure).

score -1 · Answer 3 · answered Feb 14 '12 at 20:57

I would just create a nice list. Then with CSS format the list, see example. The dashes can be done with background image. I used level2.png (contains one dash) and level3.png (contains 2 dashes).

The odd-even doesn't work 100% correct but the formatting of your list seems correct. You might consider adding more levels offcourse, it works the same way.

This way you have correct html, showing the relation between the data which makes it semantic. On the other side you have the flexibility to show it the way you want and it allows adding more data without additional formatting.

<html>
<head>
    <style type="text/css">
        * {
            font-size: 11px;
            font-family: tahoma;
            background-repeat: no-repeat;
        }
        .odd {
            background-color: #ccc;
        }
        .even {
        }

        ul, li {
            width: 300px;
            padding: 0px;
            padding: 0px;
        }

        ul.level1 li span {
        }
        ul.level2 li span {
            padding-left: 50px;
            background-image: url(level2.png);
        }
        ul.level3 li span {
            padding-left: 100px;
            background-image: url(level3.png);
        }
    </style>
</head>
<body>
    <?PHP
        $i=0;
        echo '<ul class="level1">';
        for($x=0; $x<10; $x++) {
            echo '<li class="'.(($i%2) ? 'odd' : 'even').'"><span>level1-'.$i.'</span>';
            $i++;
                echo '<ul class="level2">';
                for($y=0; $y<10; $y++) {
                    $i++;
                    echo '<li class="'.(($i%2) ? 'odd' : 'even').'"><span>level2-'.$y.'</span>';
                        echo '<ul class="level3">';
                        for($z=0; $z<10; $z++) {
                            $i++;
                            echo '<li class="'.(($i%2) ? 'odd' : 'even').'"><span>level3-'.$z.'</span>';

                            echo '</li>';
                        }
                        echo '</ul>';
                    echo '</li>';
                }
                echo '</ul>';
            echo'</li>';
        }
        echo '</ul>';
    ?>
</body>

The question I have is about generating the valid markup, not displaying it once I have it. Due to the complexity of my data set, there is not an easy way to predict the depth of the next item... it may need to be indented one stop from the current item, or it may need to be indented two fewer stops. — Brendon Dugan, Feb 14 '12 at 21:14

Generate Nested UL's Based Upon Variable Depth Data

3 Answers3