如何递归获取“父 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?

推荐答案

这个 网站对在 mysql 和 PHP 中存储分层数据的不同方法有一个非常好的概述.要回答您的问题，最简单的方法是使用 php 和递归.您还可以使用其他方法，例如修改的预序横向，它不需要多个数据库查询.但是在处理大量插入和更新时，这种方法实现起来可能更复杂.

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屋！