MySQL + PHP的检索叶的孩子与路径 [英] mysql + php retrieve leaf children with path

问题描述

我有一张像这样的桌子.

I have a table that like this.

+-----------+-----------+-----------+-----------+-----------+-----------+
|id         | parent_id | name      | order     | status    | date_add  |
+-----------+-----------+-----------+-----------+-----------+-----------+
|1          | 0         | shoes     | 1         | 1         | 2011-04-02|
+-----------+-----------+-----------+-----------+-----------+-----------+
|2          | 1         | male      | 2         | 1         | 2011-04-02|
+-----------+-----------+-----------+-----------+-----------+-----------+
|3          | 1         | female    | 3         | 1         | 2011-04-02|
+-----------+-----------+-----------+-----------+-----------+-----------+
|4          | 3         | red shoes | 4         | 1         | 2011-04-02|
+-----------+-----------+-----------+-----------+-----------+-----------+

我只想选择叶子的孩子及其路径.

I want to select only the leaf children, with their path.

我想得出如下结果:

+------+-------------------------------------+
| 2    | shoes/male                          |
+------+-------------------------------------+
| 4    | shoes/female/red shoes              |
+------+-------------------------------------+

如果这不仅是sql，还可以是php + sql

if this does not only sql can also be php + sql

请帮助我.

推荐答案

一种非常简单的解决方案，可以使用PHP打印出ID和到所有最后一个子节点的路径，因为我不知道在MySQL中这样做的方法.希望这会有所帮助！

Very simple solution to print out id and path to all last child nodes using PHP as I am not aware of a way of doing this within MySQL. Hope this helps!

function getChildren($parent= "", $x = 0) {
   $sql = "SELECT id, name FROM recurr WHERE parentId = $x";
   $rs = mysql_query($sql);
   //echo "Name: $parent has ". mysql_num_rows($rs)." children<br/>";
   while ($obj = mysql_fetch_object($rs)) {
      if (hasChildren($obj->id)) {
         getChildren($parent."/".$obj->name, $obj->id);
      } else {
         echo $obj->id .", ".$parent."/".$obj->name."<br/>";
      }
   }
}

function hasChildren($x) {
   $sql = "SELECT * FROM recurr WHERE parentId = $x";
   $rs = mysql_query($sql);
   if (mysql_num_rows($rs) > 0) {
      return true;
   } else {
      return false;
   }
}

要运行，只需致电:

getChildren();

这篇关于MySQL + PHP的检索叶的孩子与路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！