PHP/MySQL - building a nav menu hierarchy

Question

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...

cletus · Answer 1 · 2009-01-25T16:15:01.743

Hierarchical data is somewhat annoying in a relationsal database (excluding Oracle, which has operators in START WITH/CONNECT BY to deal with this). There are basically two models: adjacency list and nested sets.

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 Superfish to turn that list into a menu.

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. :)

The hardest part for me is figuring out how to structure the array/object. Could you point me to an example? — Matt, Jan 25 '09 at 15:33
@Matt check out cakePHP's implementation ACL, specifically the Tree behavior...its a little abstract but still should be helpful. http://api.cakephp.org/class_tree_behavior.html — David, Jan 25 '09 at 16:36

Paul Dixon · Accepted Answer · 2009-01-25T15:24:51.673

7

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.

edited Jan 25 '09 at 15:24

answered Jan 25 '09 at 15:16

Paul Dixon

295,876
54
310
348

1

This model is error-prone when it comes to insertion of new elements or moving items around and IMHO not worth the supposed advantages. – cletus Jan 25 '09 at 23:14
@cletus - Its not meant to be rapidly updated constantly, still if you use transactions and stored procedures, then update/insert related errors go away. – David Jan 25 '09 at 23:45
What about updating the index when you add nodes etc. On a large dataset and rapid additions it would mean a whole lot of updates to the index – arnorhs Nov 27 '09 at 10:41

score 1 · Answer 3 · edited May 23 '17 at 11:43

1

I just posted in a similar question my own approach to transform MySQL hierarchical data (adjacency list) into a menu (HTML)

It does not use recursion. And it requires a single query to the database.

Read more at

https://stackoverflow.com/questions/2871861#3368622

Thanks.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 30 '10 at 04:23

J. Bruni

20,322
12
75
92

score 1 · Answer 4 · edited Oct 30 '11 at 21:02

1

The way your storing hierarchical data isn't as efficient as you might want. I read the article Managing Hierarchical Data in MySQL a few years ago and have since found it as the best solution to managing hierarchy based data in SQL. Next best benefit is that I believe you can grab the entire tree with one query.

edited Oct 30 '11 at 21:02

Paŭlo Ebermann

73,284
20
146
210

answered Jan 25 '09 at 16:02

David

17,673
10
68
97

score 0 · Answer 5 · answered Jan 25 '09 at 16:51

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

PHP/MySQL - building a nav menu hierarchy

5 Answers5

Linked