您好，我有一个数据库表，我想设置为一个树状结构，并得到该树的叶子节点。

hi i have a database table , i want to set that as a tree structure and get the leaf nodes of that tree .

在此表中，我有 preferenceID 和 preferenceParentID 。

在这种情况下，我想建一棵树。

in this case i want to built a tree .

级别 1 应时尚和音乐，因为他们有 preferenceParentID = 0

level 1 should be fashion and music , because they have the PreferenceParentID = 0

在 2 第二级男装应在时尚，因为它的父preference id是时尚。和艺术家 sholud是在音乐。

in 2 nd level men's clothing should be under fashion because it's parent preference id is fashion . and Artists sholud be under music .

在 3 水平女装和牛仔裤应在男装和非洲和 afrobeat 建议立即进行删除是在艺术家。

in 3 level couture and denims should be under men's clothing and african and afrobeat shoul be under Artists.

和我想要得到的所有的叶节点值。在这种情况下，我想获得

and i want to get all the leaf node values . in this case i want to get

女装和牛仔裤和非洲和 afrobeat`

couture and denims and africanandafrobeat`.

树可以长到n层。

请帮助我。任何建议，欢迎.......................：D

please help me . any suggestion is welcome ....................... :D

推荐答案

在回应肖汉的链接文章中，我要发布一个更简单的解决办法：

In response to Chauhan's linked article, I'd like to post a much simpler solution:

// sample data (from one big query selecting them in one go)
$rows = array(
  array('id' => 971,  'parent_id' =>   3, 'label' => 'Genres'),
  array('id' => 972,  'parent_id' =>   3, 'label' => 'Movie Stars'),
  array('id' => 1,    'parent_id' =>   0, 'label' => 'Fashion'),
  array('id' => 32,   'parent_id' =>   1, 'label' => 'Men\'s Clothing'),
  array('id' => 45,   'parent_id' =>  32, 'label' => 'Couture'),
  array('id' => 55,   'parent_id' =>  32, 'label' => 'Denims'),
  array('id' => 2,    'parent_id' =>   0, 'label' => 'Music'),
  array('id' => 970,  'parent_id' =>   2, 'label' => 'Artists'),
  array('id' => 1118, 'parent_id' => 970, 'label' => 'African'),
  array('id' => 1119, 'parent_id' => 970, 'label' => 'Afrobeat'),
);

// build map and collect ids
$map = array();
$ids = array();
foreach ($rows as $row) { // one could use the typical mysql_fetch_* stuff here 
  if (!isset($map[$row['parent_id']])) {
    $map[$row['parent_id']] = array();
  }

  $map[$row['parent_id']][] = $row;
  $ids[] = $row['id'];
}

// recursive helper display
function helper($map, $parentId = 0) {
  echo '<ul>';
  foreach ($map[$parentId] as $entry) {
    printf('<li>[%s] %s', $entry['id'], $entry['label']);
    if (isset($map[$entry['id']])) {
      helper($map, $entry['id']);
    }
    echo '</li>';
  }

  echo '</ul>';
}

// create ul
helper($map);

// the leaf nodes
print_r(
  array_diff($ids, array_keys($map))
);

我也想说，即，如果不能避免这样的数据库结构，递归查询可能是最糟糕的事情，性能明智的。

I also like to say, that, if such database structures cannot be avoided, recursive queries is probably the worst thing to do, performance wise.