我正在尝试创建一种方法来检索和显示无数的嵌套类别。
现在我可以像这样选择它们（未经测试，不重要）：
$resulttable1 = mysql_query("SELECT name, id FROM categories WHERE childof=0");
while($rowtable1 = mysql_fetch_array($resulttable1)){
$cat1 = $rowtable['name'];
$resulttable2 = mysql_query("SELECT name, id FROM categories WHERE childof=$rowtable1[id]");
while($rowtable2 = mysql_fetch_array($resulttable2)){
$cat2 = $rowtable2['name'];
$resulttable3 = mysql_query("SELECT name, id FROM categories WHERE childof=$rowtable2[id]");
while($rowtable3 = mysql_fetch_array($resulttable3)){
$cat3 = $rowtable3['name'];
}
}
}
但是，如果用户想要超过 3 个“级别”的嵌套怎么办？如何使 mysql SELECT 能够检索无限量的嵌套类别？
更新：
好的，使用the link http://www.sitepoint.com/hierarchical-data-database-2/保罗提供我是这样完成的：
lft 和 rgt 字段如何工作并不重要，因为它们会在您插入和删除类别时自动更新。不过，找出答案很有趣。此外，第一个条目是静态值。它基本上只是树的开始，只需保持原样即可。我下面的脚本不会根据其标题“产品”来回应它。
<?php
include_once("config.php");
display_tree('products');
function display_tree($root) {
echo'<table>';
// retrieve the left and right value of the $root node
$result = mysql_query('SELECT lft, rgt FROM tree '.
'WHERE title="'.$root.'";');
$row = mysql_fetch_array($result);
// start with an empty $right stack
$right = array();
// now, retrieve all descendants of the $root node
$result = mysql_query('SELECT title, lft, rgt FROM tree '.
'WHERE lft BETWEEN '.$row['lft'].' AND '.
$row['rgt'].' ORDER BY lft ASC;');
// display each row
while ($row = mysql_fetch_array($result)) {
// only check stack if there is one
if (count($right)>0) {
// check if we should remove a node from the stack
while ($right[count($right)-1]<$row['rgt']) {
array_pop($right);
}
}
// display indented node title
$repeatamount = (count($right)) - 1;
if($repeatamount < 0){ $repeatamount = 0; }
if($row['title'] != 'products'){
echo'<tr>';
echo "<td>".str_repeat('-->',$repeatamount ).$row['title']."</td>";
echo'</tr>';
}
// add this node to the stack
$right[] = $row['rgt'];
}
echo'</table>';
}
?>
这将显示类似以下内容：
这是插入新类别的示例：addnew.php?parent=3&title=Shooter
这将在“PC”下添加“射击游戏”类别（而“PC”又在“游戏”下）。
<?php
include_once("config.php");
if(isset($_GET['parent']) && is_numeric($_GET['parent']) && isset($_GET['title']))
{
$parent = $_GET['parent'];
$title = mysql_real_escape_string($_GET['title']);
mysql_query("INSERT INTO tree (parent, title) VALUES ('$parent', '$title')");
rebuild_tree(0, 0);
}
function rebuild_tree($parent, $left) {
// the right value of this node is the left value + 1
$right = $left+1;
// get all children of this node
$result = mysql_query('SELECT id FROM tree '.
'WHERE parent="'.$parent.'";');
while ($row = mysql_fetch_array($result)) {
// recursive execution of this function for each
// child of this node
// $right is the current right value, which is
// incremented by the rebuild_tree function
$right = rebuild_tree($row['id'], $right);
}
// we've got the left value, and now that we've processed
// the children of this node we also know the right value
mysql_query('UPDATE tree SET lft='.$left.', rgt='.
$right.' WHERE id="'.$parent.'";');
// return the right value of this node + 1
return $right+1;
}
?>
我希望这可以帮助其他正在寻找同样东西的人。
