将修改后的预订树遍历模型(嵌套集)转换为<ul>

我试图将我的数据分层设置为树遍历模型到

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

这是我的代码:

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 ="
";
  $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.= "
".$row['title']."
";
      }elseif (($row["rght"] - $row["lft"] > 1) && ($row["rght"] > $p_right)){
        $result. "
";
        $result.= "
".$row['title']."
";

      }else{
        $result.= "
".$row['title']."
";
      } 

      $p_right = $row["rght"];
      $p_left = $row["lft"];
      $p_diff = $diff;
    }
  }
  $result.= "
";
  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       | 

现在它输出如下:

    

Cat 1
Cat 2
Cat 3
Cat 4
Cat 5
Cat 6
Cat 7
Cat 8

任何人都可以告诉我为什么或如何在分层结构中输出列表？

相关主题



1> Henrik Opel..：

---

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

步骤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 

while (!empty($tree)) {
  $currNode = array_shift($tree);
  // Level down?
  if ($currNode['depth'] > $currDepth) {
    // Yes, open 

    $result .= '
';
  }
  // Level up?
  if ($currNode['depth'] < $currDepth) {
    // Yes, close n open 

    $result .= str_repeat('
', $currDepth - $currNode['depth']);
  }
  // Always add node
  $result .= '
' . $currNode['title'] . '
';
  // Adjust current depth
  $currDepth = $currNode['depth'];
  // Are we finished?
  if (empty($tree)) {
    // Yes, close n open 

    $result .= str_repeat('
', $currDepth + 1);
  }
}

print $result;

与递归函数相同的逻辑:

function renderTree($tree, $currDepth = -1) {
  $currNode = array_shift($tree);
  $result = '';
  // Going down?
  if ($currNode['depth'] > $currDepth) {
    // Yes, prepend 

    $result .= '
';
  }
  // Going up?
  if ($currNode['depth'] < $currDepth) {
    // Yes, close n open 

    $result .= str_repeat('
', $currDepth - $currNode['depth']);
  }
  // Always add the node
  $result .= '
' . $currNode['title'] . '
';
  // Anything left?
  if (!empty($tree)) {
    // Yes, recurse
    $result .=  renderTree($tree, $currNode['depth']);
  }
  else {
    // No, close remaining 

    $result .= str_repeat('
', $currNode['depth'] + 1);
  }
  return $result;
}

print renderTree($tree);

两者都将输出以下结构:

    
Cat 1
    

        

            
Cat 2
            
Cat 3
            

                

                    
Cat 4
                
            
            
Cat 5
            

                

                    
Cat 6
                    

                        

                            
Cat 7
                        
                    
                
            
            
Cat 8
        
    

---

Nitpickers角落:提问者明确要求

,但订购了无序列表!？加油......
;-)

---

@Henrik Opel ..我测试了你的递归函数,但效果不好......子树(UL)必须在
• 标签内.不在它之外...在你的代码中"$ result.='
• '.$ currNode ['title'].''"
• 标签已关闭,它已被打开.
  
  2> 小智..：

  ---

  更好的渲染树函数对我有用(php函数准备用于jsTree jQuery插件的html源代码)而不是Henrik Opel的一个:

  function MyRenderTree ( $tree = array(array('name'=>'','depth'=>'')) ){
  
  $current_depth = 0;
  $counter = 0;
  
  $result = '
  ';
  
  foreach($tree as $node){
      $node_depth = $node['depth'];
      $node_name = $node['name'];
      $node_id = $node['category_id'];
  
      if($node_depth == $current_depth){
          if($counter > 0) $result .= '';            
      }
      elseif($node_depth > $current_depth){
          $result .= '
  ';
          $current_depth = $current_depth + ($node_depth - $current_depth);
      }
      elseif($node_depth < $current_depth){
          $result .= str_repeat('
  ',$current_depth - $node_depth).'';
          $current_depth = $current_depth - ($current_depth - $node_depth);
      }
      $result .= '
  ';
      ++$counter;
  }
   $result .= str_repeat('
  ',$node_depth).'

'; $result .= '
'; return $result;}

结果HTML:

    
 ELECTRONICS
        

            
 TELEVISIONS
                

                    
 TUBE
                    
 LCD
                    
 PLASMA
                        

                            
 PLASMA1
                            
 PLASMA2
                        
                    
                
            
            
 PORTABLE ELECTRONICS
                

                    
 MP3 PLAYERS
                        

                            
 FLASH
                        
                    
                    
 CD PLAYERS
                    
 2 WAY RADIOS