如何从mysql获取分层菜单 [英] how to get the hierarchical menu from mysql

问题描述

我有一张桌子，上面有类似层次的菜单

I have a table having hierarchical menus like

"id" "parent_id" "name"
1 0 menu
2 1 item1
3 2 item1_1
4 1 item2
5 4 item2_1
...
...

，我这里有100多个菜单项.为了获得数组中的所有项目，我必须编写一个像这样的递归函数

and I have 100s of menu items here. In order to get all items in an array I have to write a recursive function like this

getmenu function(parent_id = 1)
{
  $items = mysql_query("SELECT id FROM table WHERE parent_id = " + parent_id);
  while ($item = msyql_Fetch_assoc($items)) {
    ...here I put them in array and call recursive function again to get sub items...
    getmenu($item['id']);
  }   
}

但是这会执行100条查询.这是从数据库中获取分层菜单的最佳方法吗?这种方式会加载很多mysql吗?

but this executes 100s of queries. Is this the best way to do this, to get hierarchical menus from database? Does this way loads mysql much?

推荐答案

$stmt = "SELECT id, parent_id FROM table";
$items = Array();
$result = mysql_query($stmt);

while ($line = mysql_fetch_assoc($result)) {
    $items[] = $line;
}

$hierarchy = Array();

foreach($items as $item) {
    $parentID = empty($item['parent_id']) ? 0 : $item['parent_id'];

    if(!isset($hierarchy[$parentID])) {
        $hierarchy[$parentID] = Array();
    }

    $hierarchy[$parentID][] = $item;
}

根级别将为$hierarchy[0].键是项ID，值都是直接子级.

The root level will be $hierarchy[0]. Keys are items ids and values are all direct children.

这篇关于如何从mysql获取分层菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！