Logistical question about dynamically building navigation

[bothwell]

So I'm building a CMS - users will have the option to add new 'pages' (just a record in the database accessed by a page name slug, standard stuff). I want them to also be able to specify where they want the pages to appear in the navigation system, which works using lists with up to three children. My thinking now is that I give the pages a column in the db with an int flag, and the flag will determine whether a page should be level one, level two, or level three on the navigation system. I'll also need to find a way to link parents with children. I am now getting myself into a very tangled mess where I'm not at all sure what the best way to proceed is, lol.

 

I'm thinking along the lines of this:

SELECT * FROM table WHERE level = 1 
{ 
while is_array {
print '<li>'.$pagename.' '; 

SELECT * FROM table WHERE level = 2
if num_rows=0 { print '</li>'; }
else { 
while is_array { print '<ul><li>'.$pagename.' '; }

SELECT * FROM table WHERE level = 3
if num_rows=0 { print '</li>'; }
else { 
while is_array { print '<ul><li>'.$pagename.'</li>'; }
}}}
print '</li></ul>';
} 

 

(obviously that's sort of half psuedocode, it needs tidying up). My problem with this approach is that I'm using three separate queries to basically do the same thing, and it just seems really clunky. It also doesn't take into account which child belongs to which parent, because I seem to be doing it all wrong. Maybe map the data in a separate table ( pageid=1 has child [pageid=2,pageid=3]) and do a join on those selects above to narrow down the selections? Not really sure what's best, here.

 

I kinda have the feeling that this should be a relatively trivial issue and that I'm wildly over-engineering it and that's why I'm getting so confused. Does anybody else have any more sensible or efficient suggestions?

[JasonLewis]

Well think about it, you would need another column called something along the lines of "parent_id". So you would loop through all parent pages first, then you would need to find any pages with the parent_id of that page and so on.

You could also do it with one query and just add everything to an array as you loop through, then another loop to display the data. But if you look at the bottom of this forum you can see how many queries are performed on one page by SMF.