Help with MySQL solutions for site navigation

[latinomigs]

Hello,

My overall goal is to build a website generated by PHP with a MySQL database storing content. From experimenting I have a good idea of how to do most of it. Where I'm confused is with the organization and generation of site navigation.

My site nav is split into two sections:

• A primary nav bar with 6 links
• A left column with any number of secondary and tertiary navigation

An example of the output markup for the primary nav is:

<div id="priNav">
<ul>
<li><a href="#">Primary Link One</a></li>
<li><a href="#">Primary Link Two</a></li>
<li><a href="#">Primary Link Three</a></li>
<li><a href="#">Primary Link Four</a></li>
</ul>
</div>

And an example of the output markup for the secondary/tertiary nav is:

<div id="secNav">
<ul>
<li><a href="#">Secondary Link One</a></li>
<li><a href="#">Secondary Link Two</a>
<ul>
<li><a href="#">Tertiary Link One</a></li>
<li><a href="#">Tertiary Link Two</a></li>
</ul>
</li>
<li><a href="#">Secondary Link Three</a></li>
</ul>
</div>

My first attempt at organizing the db tables for navigation resulted in the following table structures:

dbt_nav_root

id = The primary key for this table
cont_id = A foreign key that links to the primary key of the content table
text = The anchor tag text
is_parent = a boolean designating whether this table has child links

dbt_nav_child1,

dbt_nav_child2

id = The primary key for this table
cont_id = A foreign key that links to the primary key of the content table
parent_id = A foreign key that links to the primary key of it's parent link
text = The anchor tag text
is_parent = a boolean designating whether this table has child links

I'm not sure how to measure the success of this structure because I can't find anything to compare it to. I guess it's been successful because I was able to make it work, but I'm anxious to validate and improve my attempt through comparison and your advice.

So how do you guys handle this sort of thing?

[leatherback]

Hi,

I use one table.
[id][label][link][parent][order]

Parent is the ID of the parent of that entry, with 0 for root-level entries. After retrieving all the pages (Mind you: typically I do not have many pages). A recursive function then trails the array in which I store all entries, and builds the tree using the root values as a starting point. the order is a sorting order, unique for the thread, and is used to determine the order in which elements will appear.

[latinomigs]

Would there be any reason NOT to include a "level" column in that one table with a value that indicates it's position in the navigation hierarchy?

[leatherback]

Because you do not really need it? You can build a branch from the root up. Unless you want to create sub-branches at a certain level only.

I keep track of the level in the recursive function that builds the branches. If you search for my nick & recursive or something, you should be able to find the function; I posted it some time ago as an example

[leatherback]

hm.. unusally helpfull today. Must be the sun & start of weekend.

This thread should help:
http://www.phpbuilder.com/board/showthread.php?s=&postid=10633599#post10633599

[ClarkF1]

I found this article @ sitepoint useful

[latinomigs]

Thanks guys, these links have been really helpful, but still I'm having trouble figuring out how to make the recursive function output a nested list at the next level. As in the example:

<div id="secNav">
<ul>
<li><a href="#">Secondary Link One</a></li>
<li><a href="#">Secondary Link Two</a>
<ul>
<li><a href="#">Tertiary Link One</a></li>
<li><a href="#">Tertiary Link Two</a></li>
</ul>
</li>
<li><a href="#">Secondary Link Three</a></li>
</ul>
</div>

I still think this might be easier with a level column in my table, but I would really like to get this recursive function thing down. Thanks for your help!