PHP/MySQL - building a nav menu hierarchy

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

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

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

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

Thanks.