使用嵌套集模型进行 SQL 查询和 PHP 操作 [英] SQL Query and PHP manipulation with Nested Set Model

问题描述

我正在阅读这篇文章，http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/.

I was reading this article, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/.

我想举一个简单的例子，然后问你我如何得到想要的结果?所以这里是例子:

I wanted to give a simple example and then ask you how do I get the desired result? So here is the example:

+---------+-----------------------------+
|  product_id | product_name            |
+---------+-----------------------------+
|           1 | Example Product         |
+---------+-----------------------------+
+---------+-----------------------------+
|  product_id | category_id             | 
+---------+-----------------------------+
|           1 | 2                       |
|           1 | 4                       |
+---------+-----------------------------+
+-------------+--------------------+------+------+
| category_id | name                 | lft | rgt |
+-------------+--------------------+------+------+
|           1 | Electronics          |  1  |  8  |
|           2 | Televisions          |  2  |  3  |
|           3 | Portable Electronics |  4  |  7  | 
|           4 | CD Players           |  5  |  6  |
+-------------+--------------------+------+------+

我希望在PHP中查询然后操作数据后能够在HTML中显示以下结果:

I want to be able to display the following result in HTML after querying and then manipulating the data in PHP:

"Example Product" Categories:
Electronics
    Televisions 
    Portable Electronics
        CD Players

你能帮助我通过 PHP 中的查询和操作来实现这个结果吗?

Can you help walk me through the query and manipulation in PHP to achieve this result?

一些需要考虑的细节:

1. 注意这两个类别都在 Electronics 下，但Electronics"在这里只出现一次，显示其所属的每个子类别
2. 结果最终应该是一个 PHP 多维数组，其中类别包含子类别数组，每个子类别包含子子类别数组(如果存在).

我认为打印深度对于在 HTML 中构建正确的树非常重要.

I imagine printing the depth will be very important for constructing the right tree in HTML.

推荐答案

我认为这是一个很好的挑战 .. 这是我的解决方案:

I thought this was a nice challenge .. here's my solution:

基本上:读取一个节点，然后rgt 小于您的rgt 的所有后续节点都是您的子节点，递归执行此操作.我使用 peek/consume 像往常一样从 mysql 中读取数据.

Basically: read a node, then all following nodes with a rgt smaller than your rgt are your children, do this recursively. I've used a peek/consume to read from mysql like you normally would.

如果查询没有给出结果，或者数据集损坏，脚本将中断或循环.

The script will break or loop if the query gives no results, or if the data-set is broken.

class NestedNodeReader {

    private $mysql_result;
    private $peeked = false;
    private $last_peek;

    public function __construct($mysql_result) {
        $this->mysql_result = $mysql_result;
    }

    public function getTree() {
        $root = $this->consume();
        $root["children"] = $this->getSubTree($root["rgt"]);
        return $root;
    }

    private function getSubTree($stop_at) {
        $nodes = array();
        $node = $this->peek();
        while ($node["rgt"] < $stop_at) {
            $node = $this->consume();
            $node["children"] = $this->getSubTree($node["rgt"]);
            $nodes[] = $node;
            $node = $this->peek();
            if (false === $node) {
                break;
            }
        }
        return $nodes;
    }

    private function peek() {
        if (false === $this->peeked) {
            $this->peeked = true;
            $this->last_peek = mysql_fetch_assoc($this->mysql_result);
        }
        return $this->last_peek;
    }

    private function consume() {
        if (false === $this->peeked) {
            return mysql_fetch_assoc($this->mysql_result);
        } else {
            $this->peeked = false;
            return $this->last_peek;
        }
    }
}

$query = "SELECT node.name, node.lft, node.rgt
    FROM nested_category AS node,
        nested_category AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
    ORDER BY node.lft;"
$mysql_result = mysql_query($query);
$nnr = new NestedNodeReader($mysql_result);
print_r($nnr->getTree());

这篇关于使用嵌套集模型进行 SQL 查询和 PHP 操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！