嵌套多级类别的更动态的方法 [英] A more dynamic way of nesting multi-level categories
问题描述
我正在尝试创建一种检索和显示无数嵌套类别的方法.
现在，我可以像这样选择它们(未经测试，不重要):
$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检索无数的嵌套类别?
更新: 好的，使用链接 paul，前提是我做到了:/p>
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"(反过来又在"Games"(游戏)下)中添加"Shooter"(射击)类别.
<?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;
}
?>
我希望这可以帮助其他人寻找相同的东西.
如果您使用childof = 0来表明该类别是一个无子级的子代，则可以倒退直至达到0.因此，从:
id name childof
7 Motor-Racing 6
您将有一个while循环，该循环将一直持续到rowtable ['childof']值为0.
I am trying to create a way to retrieve and display an endless amount of nested categories.
Now I could select them like this (not tested, not important):
$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'];
}
}
}
But what if a user wants more than 3 "levels" of nesting? How can I make the mysql SELECT in a way that it retrieves an endless amount of nested categories?
UPDATE: Ok, using the link paul provided I got it done like this:
It's not really important how the lft and rgt fields work as they get updated automatically when you insert and remove categories. It's interesting to find out, though. Also, the first entry is a static value. It's basically just the start of the tree, just leave it in as-is. My script below doesn't echo it based on it's title "products".
<?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>';
}
?>
Which will display something like:
And this is an example for inserting a new category: addnew.php?parent=3&title=Shooter which would add the category "Shooter" under "PC" (which is in turn under "Games").
<?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;
}
?>
I hope that helps anyone else looking for the same thing.
If you use childof = 0 to show that the category is a child of no-one you would be able to go backwards until you reached 0. So starting at:
id name childof
7 Motor-Racing 6
You would have a while loop that would continue until you got a rowtable['childof'] value of 0.
这篇关于嵌套多级类别的更动态的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
