php - 嵌套多级类别的更动态的方式

Question

我正在尝试创建一种方法来检索和显示无穷无尽的嵌套类别。

现在我可以像这样选择它们(未测试，不重要):

$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保罗假设我是这样完成的:

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”下添加类别“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;     
    }

    ?>  

我希望这能帮助其他寻找同样东西的人。

Answer 1

如果您使用 childof = 0 来表明该类别是 no-one 的子类别，您可以倒退直到达到 0。因此从:

id  name           childof
7   Motor-Racing   6

您将有一个 while 循环，该循环会一直持续到您的 rowtable['childof'] 值为 0 为止。