Building dynamic multi level menu

[irandoct]

Dear Friends,
I want to create a dynamic database driven multilevel menu. i used the following class to do that. can any one help me reduce my codes and database queries?! my menu supports 5 levels.

*** my menu works now! but i think i used more queries?!
my SQL database:

CREATE TABLE `menus` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`LanguageID` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`LimitedTO` int(11) DEFAULT NULL,
`PageID` text COLLATE utf8_unicode_ci,
`TableName` text COLLATE utf8_unicode_ci,
`ParentID` int(11) NOT NULL DEFAULT '0',
`MenuTitle` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`MenuLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SortOrder` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

//main menu Class
class mainmenu {

function display_children($parent) {
global $conn, $Security, $Language;
$sSqlWrk = "SELECT * from menus WHERE ParentID = ".$parent;
$rswrk =$conn->Execute($sSqlWrk);
$blockcontent = "<ul id=\"treemenu\" class=\"treeview\">";
while (!$rswrk->EOF) {
$child = ew_ExecuteScalar("SELECT COUNT(*) FROM menus WHERE ParentID = '".$rswrk->fields["ID"]."'");
if ($child == 0){
$blockcontent .= "<li><a href=\"".$rswrk->fields["MenuLink"]."\">".$rswrk->fields["MenuTitle"]."</a></li>";
}else{
$blockcontent .= "<li><a href=\"".$rswrk->fields["MenuLink"]."\">".$rswrk->fields["MenuTitle"]."</a><ul>";
//
$sSqlWrk2 = "SELECT * from menus WHERE ParentID = ".$rswrk->fields["ID"];
$rswrk2 =$conn->Execute($sSqlWrk2);
while (!$rswrk2->EOF) {
$child2 = ew_ExecuteScalar("SELECT COUNT(*) FROM menus WHERE ParentID = '".$rswrk2->fields["ID"]."'");
if ($child2 == 0){
$blockcontent .= "<li><a href=\"".$rswrk2->fields["MenuLink"]."\">".$rswrk2->fields["MenuTitle"]."</a></li>";
}else{
$blockcontent .= "<li><a href=\"".$rswrk2->fields["MenuLink"]."\">".$rswrk2->fields["MenuTitle"]."</a><ul>";
///
$sSqlWrk3 = "SELECT * from menus WHERE ParentID = ".$rswrk2->fields["ID"];
$rswrk3 =$conn->Execute($sSqlWrk3);
while (!$rswrk3->EOF) {
$child3 = ew_ExecuteScalar("SELECT COUNT(*) FROM menus WHERE ParentID = '".$rswrk3->fields["ID"]."'");
if ($child3 == 0){
$blockcontent .= "<li><a href=\"".$rswrk3->fields["MenuLink"]."\">".$rswrk3->fields["MenuTitle"]."</a></li>";
}else{
$blockcontent .= "<li><a href=\"".$rswrk3->fields["MenuLink"]."\">".$rswrk3->fields["MenuTitle"]."</a><ul>";
///
$sSqlWrk4 = "SELECT * from menus WHERE ParentID = ".$rswrk3->fields["ID"];
$rswrk4 =$conn->Execute($sSqlWrk4);
while (!$rswrk3->EOF) {
$blockcontent .= "<li><a href=\"".$rswrk4->fields["MenuLink"]."\">".$rswrk4->fields["MenuTitle"]."</a></li>";
$rswrk4->MoveNext();
}
$blockcontent .= "</ul></li>";
}
$rswrk3->MoveNext();
}
///
$blockcontent .= "</ul></li>";
}
$rswrk2->MoveNext();
}
//
$blockcontent .= "</ul></li>";
}
$rswrk->MoveNext();
}
$blockcontent .= "</ul>";
$blockcontent .= "<script type=\"text/javascript\">
ddtreemenu.createTree(\"treemenu\", true, 5)

</script>";
return $blockcontent;

}

}

---

//menu block

<?php
$mainmenu = new mainmenu;
$blockcontent = $mainmenu->display_children(0);
?>

can i use only one query to create this menu? any idea?

thanks \
Mansour

[samuelcook]

the problem that i had with listing children is that: because mysql is a relationship table i was having trouble with generating child data in the same table. so i ended up just waiting and using php to create a tree from the data that was parsed.

The following is a snippet from what I use on one site, to create a tree, almost as a wordpress tree style.

A couple of notes:
1, I'm using mysqli NOT mysql
2, conn() is db connect
3, $front is front end
4, resToArray() is ->>>>> mysqli_fetch_assoc (NOT mysqli_fetch_array)
5, getCategory() is a seperate fn.

function getCategories($front=false){
	$conn = conn();
	if($front){
		$condSQL = "WHERE category_enabled='1' ORDER BY category_name";
	}
	$sql = "SELECT * FROM categories $condSQL";
	$res = $conn->query($sql);
	$res = resToArray($res);
	$res = createTree($res,"category_id","category_parent");
	mysqli_close($conn);
	return $res;
}

function createTree($tree,$branch,$leaf){
	$x=0;
	while($x<count($tree)){
		if($tree[$x][$leaf]){
			$y=0;
			while($y<count($tree)){
				if($tree[$y][$branch]==$tree[$x][$leaf]){					
					array_splice($tree,($y+1),0,array($tree[$x]) );
					unset($tree[$x+1]);
					$tree = array_values($tree);
				}
				$y++;
			}
		}
		$x++;
	}
	return $tree;
}

function getDepth($category_id){
	$conn = conn();
	$depth = 0;
	$exit = false;
	while(!$exit){
		$sql = "SELECT category_id,category_parent FROM categories WHERE category_id='$category_id'";
		$res = $conn->query($sql);
		$res = mysqli_fetch_assoc($res);
		if($res['category_parent']){
			$category_id = $res['category_parent'];
			$depth++;	
		}else{
			mysqli_close($conn);
			$exit = true;
		}
	}
	return $depth;
}

getDepth was used on front end display:

<?
$categories = getCategories();
foreach($categories as $c){
	$parent = "";
	$depth = "";
	$depthNum = getDepth($c['category_id']);
	if($depthNum){
		for($x=0;$x<$depthNum;$x++){
			$depth .= "-";
		}
		$depth = '<span style="padding-left:'.$depthNum.'0px">'.$depth.'</span>';
		$parent = getCategory($c['category_parent']);
		$parent = $parent['category_name'];
	}?>
	<tr>
    	<td class="id"><?=$c['category_id']?></td>
        <td class="info"><span class="name"><?=$depth.' '.$c['category_name'].(!empty($parent) ? ('<span class="parent">Parent: ('.$parent.')</span>'):'')?></span></td>
        <td class="action"><input type="button" value="Edit" onClick="window.location.href='./?id=<?=$c['category_id']?>'" /></td>
    </tr>
<?
}?>

Again, this is just snippets from my code, if any is unclear ask..

Additionally, i don't even know if this will help, but i ran into a huge problem with listing children of parent categories in the same db table. So i thought I would share.

Cheers,
Samuel

[irandoct]

Thank you for your reply. may i ask you take a look at my codes and suggest required chages!
Regards
Mansour

[irandoct]

samuelcook;10974891 wrote:

Again, this is just snippets from my code, if any is unclear ask..
Samuel

Dear Samuel,
Thank you for your codes. may i ask you post your table scheme and database connection class. i hope i can use your code sample !

Thank You again
Mansour

[samuelcook]

function conn(){
	global $mysqli_hostname, $mysqli_username, $mysqli_password,$mysqli_database;
	$c = new mysqli($mysqli_hostname, $mysqli_user, $mysqli_password, $mysqli_database); 
	return $c ?  $c : die("<h1>Massive database failure.</h1>");
}

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(10) NOT NULL AUTO_INCREMENT,
  `category_parent` int(10) NOT NULL DEFAULT '0',
  `category_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `category_h1_tag` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `category_page_title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `category_meta_description` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `category_header_banner` text COLLATE utf8_unicode_ci NOT NULL,
  `category_text` text COLLATE utf8_unicode_ci NOT NULL,
  `category_enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=134 ;

Now while you probably won't be using a lot of the fields in the categories table, i just include the entire thing. Definitely use category_id, category_parent, category_name. It is a little redundant using the "category" prefix, but helps me as I JOIN three and four tables at a time.

Additionally, I am working on expanding the "createTree()" fn, as child categories do two things, they show up in reverse alphabetical order, and has an issue of child categories not falling into place if the category_id is changed and comes before the parent_id. Once I finish i will re-submit.

Cheers,
Samuel

[samuelcook]

function createTree($tree,$branch,$leaf){
	foreach($tree as $key=>$row){
		if($row[$leaf])
			$children[$tree[$key][$leaf]][] = $row;
		else
			$parent[] = $row;
	}
	$tree = $parent;
	unset($parent);
	$children = array_values($children);
	foreach($children as $childGroup){
		$childGroup = array_reverse($childGroup);
		foreach($childGroup as $child){
			foreach($tree as $key=>$row){
				if($row[$branch] == $child[$leaf]){
					array_splice($tree,$key+1,0,array($child));
				}
			}
		}
	}
	return $tree;
}

My testing led me to splitting up the different rows into two separate arrays (first foreach).

The parent array is a single level array that holds all categories that have no parent, sort of the "base" array for the end.

The children array is split into a two dimensional array, where we just stack each row from the tree that has a parent category. I did this by just referring to the category_parent as the key.
(eg).
If two categories both have a category_parent of 14 then we add both rows to $children[14][->row information here<-]
if a category only has category_parent of 1 then we add that row to $children[1][->row information here<-].

By doing this it separates the categories into subcategories itself, using the multi-demen array.

Next: use array_values to re-index the children array (this way we don't have $children[1], $children[14], etc...)(They re-index to [0],[1],etc...). This way we don't run into any breaks in the array.
set $tree to $parent and unset $parent.

Now the tricky part. A triple foreach 🙁
First, iterate through $children array, but since it is a multidimensional array we have to do again.
I used array_reverse, immediately after first foreach. This is because on my sqli statement I order by category_name. If i reverse the order (Zed to Alpha), then when I splice out the array, I can just continue to push the subcategory down (assuming that there multiple sub-categories) and everything comes out Alpha to Zed when the array is dumped.
Second, iterate through the second level of children array that already has the subcategories grouped for me by array.
Third, iterate through the parent(which is now tree) array and splice that bad boy open and insert my child row at an index of $key+1 of the parent array.

Return $tree.

**IMPORTANT::: THE PROBLEM WITH THIS is that if a subcategory OF A subcategory gets served up before the first sub-category gets put into the "main" array, then it either gets lost or shows up last in the array and doesn't get sorted properly.

** SOLUTION::: The category id of a sub->sub->category MUST be higher than that of the category id of a sub->category. This will usually be the case anyways because logically, not always the case, you will enter a sub->category in before you enter a sub->sub->category. Otherwise its like saying your going to have grandchildren before you have children.

Applogies, kind of lengthy, but I didn't want to restrict my db table to have to use the nested model as described on dev.mysql and have to worry about multiple top level categories and sub-categories being out of bounds, and lft and rght 's, so I think this is a feasible solution, and works in my instance. Hopes this finds its way into someone else's hands and they might find a possible solution to the problem as stated above.

Cheers,
Samuel

[irandoct]

Dear Sir,
Thank you for sharing your work. i'll test your code ...
Regards
Mansour