如何使用PHP / SQL构建树视图？
什么是最好的方法：
我的表有id ， name和parent_id列。
这是对我最后一个答案的更新，有一个计数器给每个ul嵌套'级别'类和一些评论。
任何人都可以建议如何适应这个使用表行，没有嵌套，但有一些类编号层次结构的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);
?>
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'];
}
}
MySQL已经在这个主题上创建了一篇很好的文章：在MySQL中管理分层数据
您可以使用数组创建面包屑视图样式，而无需使用递归函数。
这是我的工作代码：
首先，创建一个如下所示的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');