如何从数据库中的表生成树结构? [英] How can I generate a tree structure from a table in a database?

问题描述

我正在尝试从数据库中的表生成树结构.该表是扁平存储的，每条记录都有一个 parent_id 或 0.最终目标是生成一个选择框和一个节点数组.

I'm trying to generate a tree structure from a table in a database. The table is stored flat, with each record either having a parent_id or 0. The ultimate goal is to have a select box generated, and an array of nodes.

我到目前为止的代码是:

The code I have so far is :

function init($table, $parent_id = 0) 
{

    $sql = "SELECT id, {$this->parent_id_field}, {$this->name_field} FROM $table WHERE {$this->parent_id_field}=$parent_id ORDER BY display_order";

    $result = mysql_query($sql);

    $this->get_tree($result, 0);

    print_r($this->nodes);
    print_r($this->select);
    exit;
}

function get_tree($query, $depth = 0, $parent_obj = null)
{   
    while($row = mysql_fetch_object($query))
    {   
        /* Get node */
        $this->nodes[$row->parent_category_id][$row->id] = $row;

        /* Get select item */
        $text = "";
        if($row->parent_category_id != 0) {
            $text .= "    ";
        }
        $text .= "$row->name";
        $this->select[$row->id] = $text;

        echo "$depth $text\n";

        $sql = "SELECT id, parent_category_id, name FROM product_categories WHERE parent_category_id=".$row->id." ORDER BY display_order";

        $nextQuery = mysql_query($sql);
        $rows = mysql_num_rows($nextQuery);

        if($rows > 0) {
            $this->get_tree($nextQuery, ++$depth, $row);
        }            
    }
}

它几乎可以工作了，但还没有完全奏效.有人能帮我完成吗?

It's almost working, but not quite. Can anybody help me finish it off?

推荐答案

我认为这里是这一行:

if($row->parent_category_id != 0) {
    $text .= "    ";
}

应该是:

while ($depth-- > 0) {
    $text .= "    ";
}

你只缩进一次，而不是应该缩进的次数.

You are only indenting it once, not the number of times it should be indented.

还有这一行:

$this->get_tree($nextQuery, ++$depth, $row);

应该是:

$this->get_tree($nextQuery, $depth + 1, $row);

请注意，您可能应该遵循另一个答案中的建议，立即抓取整个表，然后立即处理它，因为通常您希望尽量减少到数据库的往返次数(有一些使用更优化的方式的用例，例如如果你有一棵非常大的树，并且选择了它的一小部分，但我怀疑这里的情况)

Note that you should probably follow the advice in the other answer though, and grab the entire table at once, and then process it at once, because in general you want to minimize round-trips to the database (there are a few use cases where the way you are doing it is more optimal, such as if you have a very large tree, and are selecting a small portion of it, but I doubt that is the case here)

这篇关于如何从数据库中的表生成树结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！