Displaying sub nav(s) from MySQL

[gevans]

Hey guys, please see the attached image with the contents of my table `pages`.

 

It contains three levels of navigation hierachy.

 

`safe_title` and `title` are for display and navigation

`level` denoted the page is hierachical level (1 highest, 3 lowest)

`order` is that page's order (related to its hierachy and group)

`parent` is the id of its parent page (0 for level 1 pages)

 

I'm trying to figure out the best way to query the database to show all sub nav links when on a specific top hierachy page. Example, when on the 'Home' page the sub nav will look as follows;

 

• Who We Are
  
  • History
    
  • Chief Exec's Column
    
  • Policies & Procedures
    

  [*]How To Find Us

  [*]Staff & Trustees

  [*]Vision for Portsmouth

  [*]Membership

  • Membership Form
    
  • Membership Benefits
    

 

Currently I'm planning on querying for everything with a parent id of 1 (for the home page), ordered by order (obviously). Combine the id's and use them in a second query to get all the level 3 pages.

 

Once I have all the pages just use php to display a level 2 page then checking if it has any child pages, if so display them also.

 

Am I missing anything here, and/or is there a better way to structure this?

 

Cheers,

gevans

 

[attachment deleted by admin]

[trq]

Theres not really a nice way (besides recursion) of doing what your after with the current schema. Have you looked at using a nested set style schema instead?

[gevans]

I just had a read of the folowing article;

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

It goes through The Adjacency List Model first, which is what I'm currently using. Then onto The Nested Set Model.

 

I've never come across this schema before. If I hadn't already started the build I would consider using it. My only concern after reading the article is the extra complication of adding/removing new pages due to re-calculating the 'left' and 'right' values (as used in the article).

 

If I ever find myself using a hierachical system without a confirmed depth this is what I will be doing, but with this project only having a 3 click depth I'll crack on with my 'adjacency list model'.

 

Thanks for the nudge though.