CODEWITHSUNDEEP
phphtmlloopsmarkupnested
Brendon Dugan
Brendon Dugan

Reputation: 2168

Generate Nested UL's Based Upon Variable Depth Data

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;

Upvotes: 5

Views: 2458

Answers (3)

Keyne Viana
Keyne Viana

Reputation: 6202

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)?

Upvotes: 8

Your Common Sense
Your Common Sense

Reputation: 157864

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).

Upvotes: 0

Luc Franken
Luc Franken

Reputation: 3014

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).

level2.png

level3.png

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>

Upvotes: -1

Related Questions