我试图将通过树遍历模型分层设置的数据放入
这是我的代码:
function getCats($) { // retrieve all children of $parent $query = "SELECT max(rght) as max from t_categories"; $row = C_DB::fetchSingleRow($query); $max = $row["max"]; $result ="<ul>"; $query = "SELECT * from t_categories where lft >=0 and rght <= $max"; if($rs = C_DB::fetchRecordset($query)){ $p_right =""; $p_left =""; $p_diff=""; while($row = C_DB::fetchRow($rs)){ $diff = $row["rght"] -$row["lft"]; if($diff == $p_diff){ $result.= "<li>".$row['title']."</li>"; }elseif (($row["rght"] - $row["lft"] > 1) && ($row["rght"] > $p_right)){ $result. "<ul>"; $result.= "<li>".$row['title']."</li>"; }else{ $result.= "<li>".$row['title']."</li>"; } $p_right = $row["rght"]; $p_left = $row["lft"]; $p_diff = $diff; } } $result.= "</ul>"; return $result; }
这是我的示例表:
|ID | TITLE | lft| rght | |1 | Cat 1 | 1 | 16 | |18 | Cat 2 | 3 | 4 | |22 | Cat 3 | 5 | 6 | |28 | Cat 4 | 7 | 8 | |34 | Cat 5 | 9 | 9 | |46 | Cat 6 | 11 | 10 | |47 | Cat 7 | 13 | 12 | |49 | Cat 8 | 15 | 14 |
现在它输出类似:
<ul> <li>Cat 1</li> <li>Cat 2</li> <li>Cat 3</li> <li>Cat 4</li> <li>Cat 5</li> <li>Cat 6</li> <li>Cat 7</li> <li>Cat 8</li> </ul>
谁能告诉我为什么或它将如何以分层结构输出列表?
好吧,让我们做一些赏金狩猎;)
步骤0-清理示例: 如前所述,示例数据已损坏,因为它未定义有效的嵌套集。如果您从应用程序中获取了此数据,则应检查插入/删除逻辑。
因此,为了进行测试,我使用了一个经过净化的版本,如下所示:( 这里是MySQL,因为它是第一个)
CREATE TABLE t_categories`( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(45) NOT NULL, `lft` INTEGER UNSIGNED NOT NULL, `rght` INTEGER UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 1',1,16); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 2',2,3); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 3',4,7); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 4',5,6); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 5',8,13); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 6',9,12); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 7',10,11); INSERT INTO t_categories (title, lft, rght) VALUES ('Cat 8',14,15);
第1步-让数据库对 嵌套集进行 排序 ,其主要是为了方便将树存储在数据库中而发明的,因为它们使查询子树,父关系以及在这种情况下特别感兴趣的顺序和深度变得非常容易:
SELECT node.title, (COUNT(parent.title) - 1) AS depth FROM t_categories AS node CROSS JOIN t_categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght GROUP BY node.title ORDER BY node.lft
这将以整齐的顺序返回您的集合,从根节点开始,并以预定顺序一直到结尾。最重要的是,它将把每个节点的深度添加为正整数,指示节点在根之下的级别(级别0)。对于上述示例数据,结果将是:
title, depth 'Cat 1', 0 'Cat 2', 1 'Cat 3', 1 'Cat 4', 2 'Cat 5', 1 'Cat 6', 2 'Cat 7', 3 'Cat 8', 1
在代码中:
// Grab ordered data $query = ''; $query .= 'SELECT node.title, (COUNT(parent.title) - 1) AS depth'; $query .= ' FROM t_categories AS node'; $query .= ' CROSS JOIN t_categories AS parent'; $query .= ' WHERE node.lft BETWEEN parent.lft AND parent.rght'; $query .= ' GROUP BY node.title'; $query .= ' ORDER BY node.lft'; $result = mysql_query($query); // Build array $tree = array(); while ($row = mysql_fetch_assoc($result)) { $tree[] = $row; }
结果数组将如下所示:
Array ( [0] => Array ( [title] => Cat 1 [depth] => 0 ) [1] => Array ( [title] => Cat 2 [depth] => 1 ) ... )
第2步-输出为HTML列表片段:
使用while循环:
// bootstrap loop $result = ''; $currDepth = -1; // -1 to get the outer <ul> while (!empty($tree)) { $currNode = array_shift($tree); // Level down? if ($currNode['depth'] > $currDepth) { // Yes, open <ul> $result .= '<ul>'; } // Level up? if ($currNode['depth'] < $currDepth) { // Yes, close n open <ul> $result .= str_repeat('</ul>', $currDepth - $currNode['depth']); } // Always add node $result .= '<li>' . $currNode['title'] . '</li>'; // Adjust current depth $currDepth = $currNode['depth']; // Are we finished? if (empty($tree)) { // Yes, close n open <ul> $result .= str_repeat('</ul>', $currDepth + 1); } } print $result;
与递归函数的逻辑相同:
function renderTree($tree, $currDepth = -1) { $currNode = array_shift($tree); $result = ''; // Going down? if ($currNode['depth'] > $currDepth) { // Yes, prepend <ul> $result .= '<ul>'; } // Going up? if ($currNode['depth'] < $currDepth) { // Yes, close n open <ul> $result .= str_repeat('</ul>', $currDepth - $currNode['depth']); } // Always add the node $result .= '<li>' . $currNode['title'] . '</li>'; // Anything left? if (!empty($tree)) { // Yes, recurse $result .= renderTree($tree, $currNode['depth']); } else { // No, close remaining <ul> $result .= str_repeat('</ul>', $currNode['depth'] + 1); } return $result; } print renderTree($tree);
两者都将输出以下结构:
<ul> <li>Cat 1</li> <li> <ul> <li>Cat 2</li> <li>Cat 3</li> <li> <ul> <li>Cat 4</li> </ul> </li> <li>Cat 5</li> <li> <ul> <li>Cat 6</li> <li> <ul> <li>Cat 7</li> </ul> </li> </ul> </li> <li>Cat 8</li> </ul> </li> </ul>
Nitpickers角落:发问者明确要求<ul>,但 订购了 无序列表!来…
<ul>