Constructing menu from nested set query

Hi everyone,

In my database i’m working with a nested set structure for my menus and now i need to construct the menu in the front-end but i can’t get it to work.

My setup
As i said above i’m using a nested set structure in may database, The table structure is as follows id,name,desc,lft,rgt and in the query i have a count value that tells me the amount of child categories for that specific category, when i get the query data i insert it into an array but every time a category has a count value higher then 0 (meaning it has children) it will add a “type” in the array that says “open” and if the value is 0 it will add a “type” that says “li” i have another array that works as a reference so that every time a category has a count higher then 0 (meaning it has children) it will take the category’s rgt (right) value and subtract 1 from it and then add it to the reference array, so when a category’s rgt value is in the array it will add the “type” to that category as “close”

To make sense of what i just said here is the function:


<?php
$run_connect	= dbconnect();
	$fullArry = array();

	$query = mysqli_query($run_connect, "SELECT node.category_id,node.category_name,node.lft,node.rgt, (COUNT(parent.category_id) - 1) AS count
								  FROM categories AS node,categories AS parent
								  WHERE node.lft BETWEEN parent.lft AND parent.rgt
								  GROUP BY parent.category_id
								  ORDER BY node.lft");
	$queryNumRows = mysqli_num_rows($query);
	if($queryNumRows > 0){
		$referenceArry = array();
		while($queryResults = mysqli_fetch_assoc($query)){
			$id = $queryResults['category_id'];
			$name = $queryResults['category_name'];
			$lft = $queryResults['lft'];
			$rgt = $queryResults['rgt'];
			$count = $queryResults['count'];
			if($count > 0){
				$parentRgt = $rgt-1;
				$referenceArry[$parentRgt] = null;
				$type = 'open';
			}else{
				$type = 'li';
			}

			if(array_key_exists($rgt, $referenceArry)){
				$type = 'close';
			}
			$fullArry[$id]['name'] = $name;
			$fullArry[$id]['type'] = $type;
		}
	}

	foreach($fullArry AS $id => $content){
		$category_name		= $content['name'];
		$sef_category_name	= str_replace(' ', '-', $category_name);
		$fancyCategoryName	= str_replace('and', '&', $category_name);
		?>

		<?php if($content['type'] == 'open'){ ?>
			<ul>
				<li><a href="category/<?php echo $sef_category_name;?>"><?php echo $fancyCategoryName;?></a></li>
		<?php }else if($content['type'] == 'li'){ ?>
				<li><a href="category/<?php echo $sef_category_name;?>"><?php echo $fancyCategoryName;?></a></li>
		<?php } ?>

		<?php if($content['type'] == 'close'){ ?>
				<li><a href="category/<?php echo $sef_category_name;?>"><?php echo $fancyCategoryName;?></a></li>
			</ul>
		<?php }?>




	<?php
	}

My problem
As you can see in the last part of the code the foreach need to construct the menu, the only problem i have is that the child categories ul is not inside the parent li and i don’t know how to fix this i have been sitting with this for a full day already, can anyone please help?

Thanks for reading.