Menu System / Page content Tables

Hi guys,

Can I have some advice on how best to populate this menu with items / pages from the db?

<li><a href="">Members</a>
	<ul>
	<li><a href="" title="">page under root level</a></li>
	<li><a href="" title="">page under root level</a></li>
	<li><a class="drop" href="" title="">page with sub pages</a>
		<ul class="left">
			<li><a href="" title="">sub menu item</a></li>
			<li><a href="" title="">sub menu item</a></li>
		</ul>
	</li>
<li><a class="drop" href="" title="">another page with sub pages</a>
		<ul class="left">
			<li><a href="" title="">sub menu item</a></li>
			<li><a href="" title="">sub menu item</a></li>
		</ul>
	</li>

	</ul>
</li>

Code (markup):

My thoughts are to create a table called 'pages' which will hold:
page_id (auto inc)
page_name (used for menu)
content (holds the html / page text)

** What I havent been able to figure out is how to associate them to different menu levels and populate the menu as I will have root level pages, pages with sub pages and their sub pages.

Any advice would be greatly appreciated

 

page_id (auto inc)
page_name (used for menu)
content (holds the html / page text)
+
parent_page_id


parent_page_id points to 0 if it's really root, otherwise it points to real page_id.

It's easy to implement such structure viewing on PHP.

 

Hi Codesome, thanx for the reply

How would parent_page_id be assigned for each menu item?

Example: If I wanted to add a new page to the menu / DB.... I would fill out a form adding the page name & content.... How would I then choose which root level menu or sub menu to add it under? I would need another table listing the root level items so I can populate a dropdown list right?

What other options would I have?

 

Use same table.
Query for dropdown list is:
select id,page_name from table;
Or
select id,page_name from table where parent_page_id=0;

 

thank you so much for your reply

My table has now been populated:

page_id mediumint(8) unsigned NOT NULL auto_increment,
parent_id int(8) NOT NULL DEFAULT 0,
menu_name varchar(80) DEFAULT NULL,
content LONGTEXT,

How would I build my query to construct the menu?
This is as far as I got LOL

$sql= "SELECT * FROM menu_pages WHERE parent_id='0'";
$result= mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($result) >= 1)  

if(mysql_num_rows($result) >= 1)    
	echo "<ul>";    
	
	while($rowsp = mysql_fetch_array($result)){        
	echo "<li>" . $rowsp['menu_name'] . "</li>";        
	
	$sql = ".......

PHP:

 

Whoo Hooo!!! I got it sorted!!

<li><a href="members-area/index.php">Members</a>

<?

$sql= "SELECT * FROM menu_pages WHERE parent_id='0'";
$result= mysql_query($sql) or die(mysql_error());
	if(mysql_num_rows($result) >= 1){ 
	echo "<ul>";

	while($row=mysql_fetch_array($result)) 
   	{
   	echo "<li><a href=\"members-area/members-page.php?id=$row[page_id]\">" . $row['menu_name'] . "</a>";
   
   	$parent_id = "$row[page_id]";
   	$sql2= "SELECT * FROM menu_pages WHERE parent_id=$parent_id";
	$result2= mysql_query($sql2) or die(mysql_error());

	if(mysql_num_rows($result2) >= 1)  {          
		echo "<ul class=\"left\">";            
	while($rowss = mysql_fetch_array($result2)){                
		echo "<li><a href=\"members-area/members-page.php?id=$rowss[page_id]\">" . $rowss['menu_name'] . "</a></li>";            
		}            
		echo "</ul>";


	}
	echo"</li>";
	}	
		
		
	
	}
	
echo "</ul>";
 


?>
	
</li>

PHP:

 

You are right. But your code is only for 2 sub-levels. Use one more loop or recursion to get unlimited sub-leves.

 

This is a fairly inefficient way of doing these things if it is to be written for doing nth level data which for a small site is unlikely to be an issue but if this grow problems of speed may happen.

Most databases are fairly poor at dealing with hierarchal data out the box (with the exception of Oracle that I only found out the other day has a rarely used function for this) but if you google hierarchal database then you will find a number of different mechanisms for creating complete trees without loops