PHP/MySQL - 构建导航菜单层次结构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/477793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP/MySQL - building a nav menu hierarchy
提问by Matt
So the final menu will look something like this:
所以最终的菜单看起来像这样：
Item B
Item B-1
Item B-1-2
Item B-1-1
Item A
SubItem A-1
SubItem A-2
Item C
Based on the following DB records:
基于以下数据库记录：
id menu_title parent_menu_id menu_level weight
1 Item A 0 1 1
2 Item B 0 1 0
3 Item C 0 1 2
4 SubItem A-2 1 2 1
5 Item B-1 2 2 0
6 Item B-1-1 5 3 1
7 SubItem A-1 1 2 0
8 Item B-1-2 5 3 0
How would I go about displaying?My guess is it'll involve storing all the items into a multidimensional array, then looping through it somehow...
我将如何进行展示？我的猜测是它将涉及将所有项目存储到一个多维数组中，然后以某种方式循环遍历它......
回答by cletus
Hierarchical data is somewhat annoying in a relationsal database (excluding Oracle, which has operators in START WITH/CONNECT BYto deal with this). There are basically two models: adjacency list and nested sets.
关系数据库中的分层数据有点令人讨厌（不包括 Oracle，它有操作符START WITH/CONNECT BY来处理这个问题）。基本上有两种模型：邻接表和嵌套集。
You've chosen adjacency sets, which is what I typically do too. It's far easier to change than the nested set model, although the nested set model can be retrieved in the correct order in a single query. Adjacency lists can't be. You'll need to build an intermediate data structure (tree) and then convert that into a list.
您选择了邻接集，这也是我通常所做的。尽管可以在单个查询中以正确的顺序检索嵌套集模型，但它比嵌套集模型更容易更改。邻接表不能。您需要构建一个中间数据结构（树），然后将其转换为列表。
What I would do (and have done recently in fact) is:
我会做的（实际上最近做过）是：
- select the entire menu contents in one query ordered by parent ID;
- Build a tree of the menu structure using associative arrays or classes/objects;
- Walk that tree to create nested unordered lists; and
- Use a jQuery plug-in like Superfishto turn that list into a menu.
- 在按父 ID 排序的一个查询中选择整个菜单内容；
- 使用关联数组或类/对象构建菜单结构树；
- 遍历该树以创建嵌套的无序列表；和
- 使用像Superfish这样的 jQuery 插件将该列表转换为菜单。
You build something like this:
你构建这样的东西：
$menu = array(
array(
'name' => 'Home',
'url' => '/home',
),
array(
'name' => 'Account',
'url' => '/account',
'children' => array(
'name' => 'Profile',
'url' => '/account/profile',
),
),
// etc
);
and convert it into this:
并将其转换为：
<ul class="menu">;
<li><a href="/">Home</a></li>
<li><a href="/account">Account Services</a>
<ul>
<li><a href="/account/profile">Profile</a></li>
...
The PHP for generating the menu array from is reasonably straightforward but a bit finnicky to solve. You use a recursive tree-walking function that builds the HTML nested list markup but will leave it's implementation as an exercise for the reader. :)
用于生成菜单数组的 PHP 相当简单，但解决起来有点麻烦。您使用递归树遍历函数来构建 HTML 嵌套列表标记，但将其实现留给读者作为练习。:)
回答by Paul Dixon
Dealing with the data structure as you have it will often involve recursion or multiple queries to build the tree.
处理您拥有的数据结构通常会涉及递归或多个查询来构建树。
Have you considered other ways of storing a hierarchy? Check out modified pre-order traversal - here's a nice PHP based article about this.
您是否考虑过其他存储层次结构的方法？查看修改后的预排序遍历 - 这是一篇关于此的基于 PHP的不错的文章。
回答by J. Bruni
I just posted in a similar question my own approach to transform MySQL hierarchical data (adjacency list) into a menu (HTML)
我刚刚在一个类似的问题中发布了我自己的将 MySQL 分层数据（邻接列表）转换为菜单（HTML）的方法
It does notuse recursion. And it requires a single queryto the database.
它并没有使用递归。它需要对数据库进行一次查询。
Read more at
阅读更多
https://stackoverflow.com/questions/2871861#3368622
https://stackoverflow.com/questions/2871861#3368622
Thanks.
谢谢。
回答by David
The way your storing hierarchical data isn't as efficient as you might want. I read the article Managing Hierarchical Data in MySQLa few years ago and have since found it as thebest solution to managing hierarchy based data in SQL. Next best benefit is that I believe you can grab the entire tree with one query.
您存储分层数据的方式并不像您希望的那样高效。几年前，我阅读了在 MySQL 中管理分层数据一文，后来发现它是在 SQL 中管理基于分层结构的数据的最佳解决方案。下一个最大的好处是我相信您可以通过一个查询获取整个树。
回答by Jas Panesar
Another simple way you can generate hierarchy if you don't want to use nested sets is to use a simple text string in front.
如果不想使用嵌套集，可以生成层次结构的另一种简单方法是在前面使用简单的文本字符串。
Item B
Item B-1
Item B-1-2
Item B-1-1
Item A
SubItem A-1
SubItem A-2
Item C
Would become
会成为
1 Item B
1.1 Item B1
1.1.1 Item B11
1.1.2 Item B12
2 Item A
2.1 Item A1
2.2 Item B2
3 Item C
The digit in front of each item could be stored in a field and parsed based on length (representing the depth of where it is) to tell you everything you need to know about where it goes.
每个项目前面的数字可以存储在一个字段中，并根据长度（表示它所在位置的深度）进行解析，以告诉您有关它去向的所有信息。
I use nested set hierarchies for more complicated stuff that requires calculation,e tc, but I find this approach has served well
我将嵌套集层次结构用于需要计算的更复杂的东西，例如 tc，但我发现这种方法效果很好
