donglinxi1467
2010-06-23 14:19
浏览 57
已采纳

如何从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
...
...

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']);
  }   
}

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?

图片转代码服务由CSDN问答提供 功能建议

我有一个包含分层菜单的表,如

 “id  “”parent_id“”name“
1 0 menu 
2 1 item1 
3 2 item1_1 
4 1 item2 
5 4 item2_1 
 ... 
 ... 
   
 \  n 

我在这里有100个菜单项。 为了获取数组中的所有项,我必须编写一个像这样的递归函数

  getmenu函数(parent_id = 1)
 {
 $ items = mysql_query(“  SELECT id FROM table WHERE parent_id =“+ parent_id); 
 while($ item = msyql_Fetch_assoc($ items)){
 ...这里我把它们放在数组中并再次调用递归函数来获取子项... \  n getmenu($ item ['id']); 
} 
} 
   
 
 

但这会执行100次查询。 这是最好的方法,从数据库中获取分层菜单吗? 这种方式会加载mysql吗?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doumi1852 2010-06-23 14:30
    已采纳
    $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;
    }
    

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

    点赞 打赏 评论
  • douyue9704 2010-06-23 14:44

    Take a look at Nested Sets if you don't mind a little more complex solution. Nested Sets have a very good SELECT performance and I assume that selecting is more important here.

    With the help of Nested Sets, complex hierarchical data can be managed in a very fashionable and elegant way.

    点赞 打赏 评论

相关推荐 更多相似问题