Dymanic Menus with PHP and MySQL

[Harvs]

I have products stored in a MySQL database with each product belonging to a category. That category may sit under one or more other categories.

What I need to be able to do it produce the site navigation based on the different categories and their parent categories.

So basically I need to be able to produce the following HTML dynamically based on the categories store in the database.

<ul>
<li>Category 1</li>
<li>Category 2</li>
<li>Sub Category 1</li>
<li>Sub Category 2</li>
<li>Sub Sub Category 1
<li>Category 3</li>
</ul>

How would you store the category information in the database and what would the PHP script look like to produce the navigation?

[Steerpike]

It's not all that easy and I'm of the opinion that laying it all straight out initially is not the best way with dynamic content.

Two reasons:
1. You don't know how deep the parent child relationship can be, so you have to arbitrarily set the number of loops you use.
2. It's dynamic, it's entirely possible that it can grow to be very large. A very large initial navigation menu is going to be fairly daunting.

I personally think that the best way is to have a parent field in your pages table.

The first layer (the parents) could be grabbed with the sql statement:
"SELECT * FROM pages WHERE parent=0"

Then, when the user clicks on one of those links you can generate the next layer of children to that link:
"SELECT * FROM pages WHERE parent=$choice"

This way it doesn't matter how deep your child/parent stucture goes, you're only querying the last choice.

I do suggest using a breadcrumb for additional navigation if you use this method though.

Steerpike

[Steerpike]

Oh, the php to display it looks a bit like this:

$rs = mysql_query($sql) or die(mysql_error());
echo "<table border=1>";
while($line = mysql_fetch_array($rs))
{
$title = $line['sectionTitle'];
$id = $line['sectionID'];
$content = $line['sectionContent'];
echo "<tr>";
echo "<td>";
echo "<a href=\"index.php?sectionID=$id\">";
echo "$title";
echo "</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";

[Harvs]

Steerpike,

I would really prefer it if I could get the whole layout in one go. Because I will be making a drop down menu with sub menus based on the results.

If it turns out to be too difficult I will do it they way you have suggested but this wouldn't be ideal.

[Steerpike]

Alright. I'll see what we can figure out.

<?
/*
First get everything that has no parent. This is our first row of pages.
*/

$sql = "SELECT * FROM pages WHERE parent = 0";
$rs = mysql_query($sql) or die(mysql_error());
while($line = mysql_fetch_array($rs))
{
//Now we loop through results.
$pageID = $line['pageID'];
$pageName = $line['pageName'];
echo "$pageName";
//While in this loop we get a new loop of this pages children.
$childsql = "SELECT * FROM pages WHERE parent=$pageID";
$childrs = mysql_query($childsql) or die(mysql_error());
while($child = mysql_fetch_array($childrs))
{
//We're now in a new loop of children.
$pageID = $child['pageID'];
$pageName = $child['pageName'];
echo "$pageName";
} //close inner while loop
} //close parent while loop.

Ok, this code example just does 2 levels...to add more you have to keep adding while loops inside each other. It's very ugly very quickly.

[Harvs]

That looks like it will do the job. There looks like there will only be 5 levels of categories at the most.

Like you said it is a bit messy and I would really like it if it wasn't limited to the number of levels but it will do for now.

I didn't think this was going to be such a difficult thing to work out because it's basically a mini site map but only using categories instead of pages. How do people normally generate dynamic site maps? and couldn't the same theory be used here?

[Steerpike]

Most use the original method I posted with a breadcrumb.

Certain sites are good to take a look at...anything with a lot of categories basically; ebay, amazon, etc. You'll see a list of single categories at a time and along the top a breadcrumb of hrefs that let's you know exactly where you are and how to go back.

I spent a long time trying to figure out how to make the second option work, and it's entirely possible that someone here can provide a better answer using a better SQL statement but in the end one of our designers just pointed out the first method which fixes the whole issue (as well as a few incidental glitches along the way).

[vBMechanic]

You just have to do it recursively. This is simplified, but I hope you get the idea.

$items = mysql_query("");

while ($item = mysql_fetch_array($items))
{

// Do first round of menu building here

echo "<li>".$item['title']."</li>";

if ($item['children']>0)
{

// Do further rounds of menu building here with this recursive function loop

checkChildren($item['parentid']);
}

}

function checkChildren($pid)
{

$items = mysql_query("");

while ($item = mysql_fetch_array($items))
{

echo "<li>".$item['title']."</li>";

if ($item['children']>0)
{

// Function calls itself if there are more child levels to go

checkChildren($item['parentid']);
}

}

You can even build on this so that it keeps track of how many levels down it goes and indents a certain amount of space for each level.

[Harvs]

Here is the working version I came up with.

The database structure

FIELD - TYPE
id - int
name - varchar
parent - int
children - enum('yes','no')

The Script

$sql = "SELECT * FROM categories ORDER BY id ASC"; 
$res = mysql_query($sql); 
while ($cur = mysql_fetch_array($res)) { 
 if ($done[$cur['id']]!= 'yes') { 
 echo '<li>' .$cur['name']. '</li>'; 
 $done[$cur['id']] = 'yes'; 
 if ($cur['children'] == 'yes') { 
 checkChildren($cur['id']); 
 } 
 } 
} 
function checkChildren($pid) { 
 global $done; 
 $childSql = "SELECT * FROM categories WHERE parent = " .$pid; 
 $childRes = mysql_query($childSql); 
 // Loop through every child for the current category 
 while ($child = mysql_fetch_array($childRes)) { 
 echo '<li>' .$child['name']. '</li>'; 
 $done[$child['id']] = 'yes'; 
 if ($child['children'] == 'yes') { 
 checkChildren($child['id']); 
 } 
 } 
} 

The Explanation

Using vBMechanic's idea of the recursive function I just added an global array called $done which kepts track of which categories have been displayed. The $done array made sure that a category was never displayed twice.

Thanks for all the help

-Harvs-