Help with MySQL solutions for site navigation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
latinomigs
Forum Newbie
Posts: 2
Joined: Wed Jun 07, 2006 1:48 pm

Help with MySQL solutions for site navigation

Post by latinomigs »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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:

[syntax="html"]<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:

Code: Select all

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


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Well you can do this with just one table. To see if it is root you just check if parent_id is null.

Edit: Oh yeah, I see you are using two tables for the secondary links an tertiary links as well.

You can still do this in one table, if the depth is fixed you can get your tree structure in one query using left joins (the table joining itself). If the depth is variable then you need to get all your records, put them into an array and pass them to a recursive function to parse it into a tree.

In one of the stickies somewhere there are some links to other threads where this has been covered in depth.
Post Reply