如何使用 PHP/SQL 构建树视图？

How to build a tree view with PHP / SQL?

提问by meleyal

What's the best way to:

什么是最好的方法：

1. Get the data from the db using a single query
2. Loop through the results building e.g. a nested unordered list

1. 使用单个查询从数据库中获取数据
2. 循环构建结果，例如嵌套的无序列表

My table has id, nameand parent_idcolumns.

我的表有id,name和parent_id列。

---

---

Here's an update to my last answer, with a counter that gives each ul a nesting 'level' class, and some comments.

这是我上一个答案的更新，带有一个计数器，为每个 ul 提供一个嵌套的“级别”类，以及一些注释。

Could anyone suggest how to adapt this to use table rows, without nesting, but with some kind of class numbering hierarchy for css/js hooks?

任何人都可以建议如何调整它以使用表格行，而不使用嵌套，但对 css/js 钩子使用某种类编号层次结构？

<?

//
// Get the data
//
include_once("inc/config.php");

$query = "SELECT c.* 
          FROM categories AS c
          ORDER BY c.id
          LIMIT 1000";          

$result = pg_query($db, $query);

//
// Load all the results into the row array
//
while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC))
{
  //
  // Wrap the row array in a parent array, using the id as they key
  // Load the row values into the new parent array
  //
  $categories[$row['id']] = array(
    'id' => $row['id'], 
    'description' => $row['description'], 
    'parent_id' => $row['parent_id']
  );
}


// print '<pre>';
// print_r($category_array);

// ----------------------------------------------------------------

//
// Create a function to generate a nested view of an array (looping through each array item)
// From: http://68kb.googlecode.com/svn-history/r172/trunk/upload/includes/application/controllers/admin/utility.php
//
function generate_tree_list($array, $parent = 0, $level = 0)
{

  //
  // Reset the flag each time the function is called
  //
  $has_children = false;

  //
  // Loop through each item of the list array
  //
  foreach($array as $key => $value)
  {
    //
    // For the first run, get the first item with a parent_id of 0 (= root category)
    // (or whatever id is passed to the function)
    //
    // For every subsequent run, look for items with a parent_id matching the current item's key (id)
    // (eg. get all items with a parent_id of 2)
    //
    // This will return false (stop) when it find no more matching items/children
    //
    // If this array item's parent_id value is the same as that passed to the function
    // eg. [parent_id] => 0   == $parent = 0 (true)
    // eg. [parent_id] => 20  == $parent = 0 (false)
    //
    if ($value['parent_id'] == $parent) 
    {                   

      //
      // Only print the wrapper ('<ul>') if this is the first child (otherwise just print the item)      
      // Will be false each time the function is called again
      //
      if ($has_children === false)
      {
        //
        // Switch the flag, start the list wrapper, increase the level count
        //
        $has_children = true;  

        echo '<ul class="level-' . $level . '">';

        $level++;
      }

      //
      // Print the list item
      //
      echo '<li><a href="?id=' . $value['id'] . '">' . $value['description'] . '</a>';

      //
      // Repeat function, using the current item's key (id) as the parent_id argument
      // Gives us a nested list of subcategories
      //
      generate_tree_list($array, $key, $level); 

      //
      // Close the item
      //
      echo '</li>';


    }

  }

  //
  // If we opened the wrapper above, close it.
  //
  if ($has_children === true) echo '</ul>';


}

// ----------------------------------------------------------------

//
// generate list
//
generate_tree_list($categories);


?>

回答by Maystro

function generate_list($array,$parent,$level)
{

  foreach ($array as $value)
  {
    $has_children=false;

    if ($value['parent_id']==$parent)
    {

      if ($has_children==false)
      {
        $has_children=true;
        echo '<ul>';
      }

      echo '<li>'.$value['member_name'].' -- '.$value['id'].' -- '.$value['parent_id'];

      generate_list($array,$value['id'],$level);

      echo '</li>';
    }

    if ($has_children==true) echo '</ul>';

    echo $value['parent_id'];
  }

}

回答by ThoKra

MySQL have created a good article on this subject: Managing Hierarchical Data in MySQL

MySQL 已经写了一篇关于这个主题的好文章：Managing Hierarchical Data in MySQL

回答by Boopathi .R

You can create a breadcrumb view style by using arrays, without using a recursive function.

您可以使用数组创建面包屑视图样式，而无需使用递归函数。

Here is my working code:

这是我的工作代码：

First, make a SQL query like this:

首先，进行这样的 SQL 查询：

$category = CHtml::listData(TblCategory::model()->findAllCategory(array(
'distinct'=>true,
'join'=>'LEFT JOIN tbl_category b on b.id = t.cat_parent',
'join'=>'LEFT JOIN tbl_category c on c.cat_parent = 0',
'order' => 'cat_name')),'id','cat_name');

I am using yii related code so you can use normal joinqueries, then form an array in a foreach()function

我正在使用 yii 相关代码，因此您可以使用普通join查询，然后在foreach()函数中形成一个数组

public function findAllCategory($condition='',$params=array())
{
    
    Yii::trace(get_class($this).'.findAll()','system.db.ar.CActiveRecord');
    $criteria=$this->getCommandBuilder()->createCriteria($condition,$params); 
    
    $category = array();
    $cat_before;
    $parent_id = array();
    $cat_before = $this->query($criteria,true); 
    
    //echo "<br><br><br><br><br><br><br>";
    
    foreach($cat_before as $key => $val)
    {
        $category[$key] = $val;
        $parent_id[$key]['cat_parent'] =$val['cat_parent'];
        $parent_id[$key]['cat_name'] =$val['cat_name']; 
        
        foreach($parent_id as $key_1=> $val_1)
        {   
            
            if($parent_id[$key]['cat_parent'] == $category[$key_1]['id'])
            {
                $category[$key]['cat_name']= $category[$key_1]['cat_name'] .' > '.  $parent_id[$key]['cat_name'];
                
            }
        }
    } 
    return $cat_before;  
}

Then you can get result using Main cat >> subcat 1 >> subcat_1 inner >> ...

然后你可以使用得到结果 Main cat >> subcat 1 >> subcat_1 inner >> ...