如何递归地获取“父ID"?这个MySQL表中的行数? [英] How can I recursively obtain the "parent ID" of rows in this MySQL table?

问题描述

我的数据库看起来像(pligg cms，示例数据)

My database looks like (pligg cms, sample data)

id  catID parentID   catName
1    1      0        location
2    2      0        color
3    3      1        USA
4    4      3        Illinois
5    5      3        Chicago
6    6      2        Black
7    7      2        Red

让我说说，我如何获取芝加哥的最高parentID，它应该是位置.

Let say, how do I get top parentID of chicago, it should be location.

我是否在php中编写了递归函数，或者在mysql中可以做到这一点?

Do I have write recursive function in php or is this doable in mysql?

推荐答案

此modified preorder transversal，它们不需要多个数据库查询.但是，在处理大量插入和更新时，此方法的实现可能会更加复杂.

This website has a really nice overview of the different methods for storing hierarchical data in mysql and PHP. To answer your question, the easiest way is to use php and recursion. There are other methods you could use such as the modified preorder transversal, which don't require multiple database queries. But this method can be more complex to implement when dealing with a lot of insertions and updates.

另一种非常酷的方法，也是我个人最喜欢的方法，是

Another really cool method and my personal favorite is the so called "closure table" / "adjacency relation" mentioned in What is the most efficient/elegant way to parse a flat table into a tree?

关于您的评论，基本上，您必须进行循环或递归函数，以选择芝加哥的父母，然后选择父母的父母，依此类推.

Regarding your comment you basically have to make a loop or a recursive function that selects the parent of chicago, then the parent of the parent and so forth.

$stack = array();
$parent = 3;
while($parent != 0){
    $data = (put your mysql to get the row with parentID = $parent)
    $parent = data['parentID'];
    $stack[] = $data;
}

$stack = array_reverse($stack);

然后堆栈将包含芝加哥的父母(即美国的位置)

Stack will then contain the parents of Chicago, (ie. location, USA)

这篇关于如何递归地获取“父ID"?这个MySQL表中的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！