具有单个查询的递归类别? [英] Recursive categories with a single query?

问题描述

我有一个包含文章和栏目的网站，每个部分都可以有一个父部分，只要他们喜欢例如:

I have a website with articles and sections, each sections can have a parent section, as much as they like for example:

subject 1
 -subject 2 
 --subject 3
 -subject 4
 --subject 5
 --subject 6
 ---subject 7
subject 8
subject 9

等等.

现在，我想递归地获取它们，通过 php 和 mysql 最有效的方法是什么?

Now, i want to fetch them recursively, what is the most efficient way to do it via php and mysql?

先进的 Tnx.

推荐答案

如果树不是太大，您可以使用一些巧妙的引用在 PHP 中简单地构建树.​​

If the tree isn't too large, you can simply build the tree in PHP using some clever references.

$nodeList = array();
$tree     = array();

$query = mysql_query("SELECT category_id, name, parent FROM categories ORDER BY parent");
while($row = mysql_fetch_assoc($query)){
    $nodeList[$row['category_id']] = array_merge($row, array('children' => array()));
}
mysql_free_result($query);

foreach ($nodeList as $nodeId => &$node) {
    if (!$node['parent'] || !array_key_exists($node['parent'], $nodeList)) {
        $tree[] = &$node;
    } else {
        $nodeList[$node['parent']]['children'][] = &$node;
    }
}
unset($node);
unset($nodeList);

这将为您提供 $tree 中的树结构，以及相应 children-slot 中的子项.

This will give you the tree structure in $tree with the children in the respective children-slot.

我们已经用相当大的树(>> 1000 个项目)完成了这项工作，它非常稳定，而且比在 MySQL 中执行递归查询要快得多.

We've done this with fairly large trees ( >> 1000 items) and it's very stable and a lot faster than doing recursive queries in MySQL.

这篇关于具有单个查询的递归类别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！