小编典典

将修改后的预排序树遍历模型(嵌套集)放入模型中

html

我试图将通过树遍历模型分层设置的数据放入

    ,以便显示在我的网站上。

    这是我的代码:

    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>
    

    谁能告诉我为什么或它将如何以分层结构输出列表?


阅读 390

收藏
2020-05-10

共1个答案

小编典典

好吧,让我们做一些赏金狩猎;)

步骤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>,但 订购了 无序列表!来…

2020-05-10