Not able to manage/create a Adjacency Model,

Ever since I switched from Coldfusion to PHP, I have not been able to create an Adjacency Model (parent => child menu). I use a method that works well for me, but I am convinced that this can be done way better. This is the situation:

Database(table)

CREATE TABLE IF NOT EXISTS `site_navigation` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `parent_id` tinyint(4) DEFAULT NULL,
  `label` varchar(64) NOT NULL,
  `item_class` varchar(24) NOT NULL DEFAULT 'nav-item',
  `link_class` varchar(48) NOT NULL DEFAULT 'nav-link',
  `url` varchar(64) NOT NULL,
  `sequence` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Database(data)

(1, NULL, 'Home', 'nav-item', 'nav-link', '/nl/', 1),
(2, NULL, 'Categorieën', 'nav-item dropdown', 'nav-link dropdown-toggle', 'javascript:void(0)', 2),
(3, NULL, 'Over', 'nav-item', 'nav-link', '/nl/over', 3),
(4, NULL, 'Contact', 'nav-item', 'nav-link', '/nl/contact', 4),
(5, 2, 'Antiek', 'nav-item', 'dropdown-item', '/nl/antiek', 1),
(6, 2, 'Boeken', 'nav-item', 'dropdown-item', '/nl/boeken', 2),
(7, 2, 'Kunst', 'nav-item', 'dropdown-item', '/nl/kunst', 3),
(8, 2, 'Curiosa', 'nav-item', 'dropdown-item', '/nl/curiosa', 4);

Base on this database example, to build the menu, I use two functions in my Model class :frowning: The first for the parent layer:

	public function main_items()
	{
		$sql = "SELECT `id`
					 , `label`
		             , `title`
					 , `item_class`
					 , `link_class`
					 , `url`
				  FROM `site_navigation` 
				 WHERE `parent_id` IS NULL";
				   
        $stmt 		= $this->pdo->query($sql);
		$results	=	$stmt->fetchAll(PDO::FETCH_ASSOC);
		$subs		=	$this->sub_items(); //submenu
		
		$return		=	'';
		foreach ($results as $main) {
			$return .= '<li class="'.$main['item_class'].'">';
				$return .= '<a class="'.$main['link_class'].'" title="'.$main['title'].'" href="'.$main['url'].'">'.$main['label'].'</a>';
				if ($main['id'] == 2) {
					$return	.=  '<div class="dropdown-menu">';
						$return	.=	$subs;
					$return .=  '</div>';	
				}				
			$return	.=  '</li>';
		}
		return $return;	
	}

And the second one to build the submenu for parent (id=2) which I call in the first function:

	public function sub_items()
	{
		$sql = "SELECT `label`
		             , `title`
					 , `item_class`
					 , `link_class`
					 , `url`
					 , `page_id`
				  FROM `site_navigation` 
				 WHERE `parent_id` = 2";
		   
        $stmt = $this->pdo->query($sql);
		$results	=	$stmt->fetchAll(PDO::FETCH_ASSOC);	
		$return		=	'';
		foreach ($results as $sub) {
			$return .=  '<a class="'.$sub['link_class'].'" title="'.$sub['title'].'" href="'.$sub['url'].'">'.$sub['label'].'</a>';
		}
		return $return;
	}

And this is the part where I’m confused about.In the above example there is only one parent / child relationship, but what if there are 3 or even more? Then I would have a proportional number of functions, and that can not and must not be the intention. Can someone please give me a push in the right direction based on the above. I really appreciate every help

Thanks in advance

Instead of having a function for every “level”, you could have a single function that you call recursively from itself for each sub item. So something along the lines of:

public function sub_items($parent_id)
{
    $sql = "SELECT     `id`
                     , `label`
		             , `title`
					 , `item_class`
					 , `link_class`
					 , `url`
					 , `page_id`
				  FROM `site_navigation`
				 WHERE `parent_id` = ?";

    $stmt 		= $this->pdo->prepare($sql);
    $stmt->execute(array($parent_id));
    $results	=	$stmt->fetchAll(PDO::FETCH_ASSOC);
    $return	    = '<...>';
    foreach ($results as $sub) {        
        $return .= $this->sub_items($sub['id']);
    }
    $return	    .= '<...>';
    return $return;
}

@cryptichorizon. So for no matter how many levels I can just use this second function? Or is this just one function serving all? If this is a second function containing the sub levels how do I call this in the first function? I ask this because of the parameter $parent_id

$subs = $this->sub_items($parent_id);

You could do it either way. You can keep the special top-level main_items() function that renders the first level and then calls sub_items() which will take care of all the sub-items recursively for as many levels deep as there are items.

Alternatively, you could get rid of main_items() completely and just have one function. When doing the first, top-level function call you can just pass through a null for the $parent_id parameter:

$complete_menu_html = $this->sub_items(null);

So on the first function call $parent_id will have a null value. Which means your query will return all items without a parent: the top-level. For each of those items the function calls itself, passing the item’s id value as the $parent_id, your query now returns all the children of this parent
 etc. etc. etc. all the way through the tree.

I should also add that to make the second option work, you will have to make a slight change your SQL query to correctly handle the null parent_id. Just change the = in the WHERE clause to a <=> (the spaceship or NULL-safe equal to operator) as in:

 WHERE `parent_id` <=> ?

Last question though; In th main function I have a if statement:

if ($main['id'] == 2) 

to define for which top level menu item a submenu should be displayed. Do I still need that?

Also in my original second function (for the sub levels) I have the html included in the foreach loop:

		foreach ($results as $sub) {
			$return .=  '<a class="'.$sub['link_class'].'" title="'.$sub['title'].'" href="'.$sub['url'].'">'.$sub['label'].'</a>';
		}

How should i do that in your example? That is the part what is confusing me all the time whenever I am trying to understand the recursive methhod

For any menu structure kept in a database we always use a B-Tree structure, it is much easier to handle.

For a Parent-Child structure that you currently use, with PHP I would strongly recommend pulling it all in one go for the main menu, and only pull submenu items in cases when you only need those. To make it easier, make certain the query pull the items in correct order.

SELECT `id`
        , `parent_id`
		, `label`
		, `title`
		, `item_class`
		, `link_class`
		, `url`
FROM `site_navigation` 
WHERE `parent_id` IS NOT NULL
ORDER BY
         parent_id ASC
         , sequence ASC;

Then just store it into a structured array that you can easily handle when outputting the data.

Example:
You store the master menu items under “master” and then each child item, refer its parent in child. The actual menu items for each is under records.

When parsing the code, start with the master, and while going over it, check the child for any records, then for each child you add check if it has any records under it etc.

array('master' => array([id], [id], [etc. i.e. each master category id in order]),
         'child' => array([parent] => array([child menu id in order]), [etc])
         'records' => array([id] => [menu items]);

In reality, if it is better using recursive when pulling the data or when showing the data depends. If it is a small website with few visitors, it most probably does not matter, but if the menu contains dynamic elements that show depending on user access level and the website has some traffic, it is always good to reduce the number of queries since they are more expensive than pure PHP code in this scenario.

@TheRedDevil. Thank you for the response. It is not about very large websites

It is precisely these kind of things that are confusing me most. (Those arrays) Based on my opening post what would be my best option

You are probably still going to want a similar if-statement because you still do want to differentiate between the top-level menu items and the ones further down the tree. Specifically you want the sub-menu items enclosed in <div class="dropdown-menu"></div>, but not the top level items.

You will want to change the if-statement though. The whole point of your new recursive function is that it should be general enough to handle any number of menu items at any depth, so we don’t want our function to refer to specific id’s as in the current statement: if ($main['id'] == 2)

The question you want your function to answer here is essentially something like: “Is the menu items I am currently generating at the top level of the menu tree or not?”. What information does the function have at any given time it was called that would tell it at what level it currently is in the menu tree?

Well, every time the function calls itself, it passes the current id as the $parent_id parameter. Except for the first time. The first time we call it we pass a null as the $parent_id. So if we want to know if we are at the top level, we can just check if $parent_id has a value of null:

if (is_null($parent_id)) {...}

For your second question, yes you would probably still put the code that builds up your anchor html in your foreach loop, right before where the function calls itself to build the children.

Putting it all together, your foreach loop could look something like:

    foreach ($results as $sub) {
        $return .= '<a class="' . $sub['link_class'] . '" title="' . $sub['title'] . '" href="' . $sub['url'] . '">' . $sub['label'] . '</a>';
        $subs = $this->sub_items($sub['id']);
        if (is_null($parent_id)) {
            // This is the top level, just return the generated sub-items
            $return	.=	$subs;
        } else {
            // This is not the top level, so put the generated sub-items in a dropdown menu div
            $return	.=  '<div class="dropdown-menu">';
            $return	.=	$subs;
            $return .=  '</div>';
        }
    }

Surely you meant escape all those values from the database using html_entities? :wink:

@cryptichorizon. Thank you for the reply and your patience but now I am a bit confused. Is this the foreach within the main function (as in the opening post):

foreach ($results as $main)

or in the sub function:

foreach ($results as $sub)

I ask this since the function name is called again in the foreach loop. When it’s the latter, how do I call it in the main function. Sorry for my ignorance, but I’m starting to mix things up now!

All good. The assumption I’ve been working with here is that we completely got rid of the main_items() function. We only have the sub_items() function which we now use to build your entire menu. You probably want to rename the sub_items() function to something more descriptive like build_menu().

Anyway, this single function contains a single foreach loop. It doesn’t really matter what you name the variable after the as in the foreach-clause. You can call it $main or $sub or anything else. Maybe you should call it $menu_item to avoid confusion. Then your foreach would look something like:

foreach ($results as $menu_item) {
        $return .= '<a class="' . $menu_item['link_class'] . '" title="' . $menu_item['title'] . '" href="' . $menu_item['url'] . '">' . $menu_item['label'] . '</a>';
        $subs = $this->sub_items($menu_item['id']);
...

Make sense?

:+1: definitely

@cryptichorizon. Thanks again for the responce. I hope it does :frowning: So would this be right?

	public function menu_items($parent_id)
	{
		$sql  = "SELECT *
				   FROM `site_navigation` 
				  WHERE `parent_id` =	?";				   
        $stmt = $this->pdo->prepare($sql);
		$stmt->execute(array($parent_id));
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		
		$return	=	'<ul class="navbar-nav ml-auto">';
		foreach ($results as $menu_item){
			$return .= '<li class="'.$menu_item['item_class'].'">';
				$return .= '<a class="' . $menu_item['link_class'] . '" title="' . $menu_item['title'] . '" href="' . $menu_item['url'] . '">' . $menu_item['label'] . '</a>';
				$subs = $this->menu_items($menu_item['id']);
				if (is_null($parent_id)) {
					$return	.=	$subs;
				} else {
					$return	.=  '<div class="dropdown-menu">';
					$return	.=	$subs;
					$return .=  '</div>';				
				}
			$return	.=  '</li>';
		}
		$return .= '</ul>';	
        return $return;

	}

Edit: Not sure what I’m doing wrong, but the above isn’t working? Hopefully @cryptichorizon or someoneelse can give me some guidance.

Thank you in advance

What isn’t working? Do you see an error? A white page? We can’t your screen from here you know :wink:

What I can tell you that when $parentId is NULL your query doesn’t work, because then it must be WHERE parentId IS NULL, not WHERE parentId = ? with ? = null

@rpkamp. Thank you for that. I have adjusted that, and the top level items are showing now but the sub items doesn’t. It just shows a empty dropdown: This is the complete function:

	public function menu_items($language_abbr)
	{
		$sql  = "SELECT *
				   FROM `site_navigation` 
				  WHERE `parent_id` IS NULL
				  	AND `lang` = ?";				   
        $stmt = $this->pdo->prepare($sql);
		$stmt->execute(array($language_abbr));
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		
		$return	=	'<ul class="navbar-nav ml-auto">';
		foreach ($results as $menu_item){
			$return .= '<li class="'.$menu_item['item_class'].'">';
				$return .= '<a class="' . $menu_item['link_class'] . '" title="' . $menu_item['title'] . '" href="' . $menu_item['url'] . '">' . $menu_item['label'] . '</a>';
				$subs = $this->menu_items($menu_item['id']);
				if (is_null($menu_item['parent_id'])) {
					$return	.=	$subs;
				} else {
					$return	.=  '<div class="dropdown-menu">';
					$return	.=	$subs;
					$return .=  '</div>';				
				}
			$return	.=  '</li>';
		}
		$return .= '</ul>';	
        return $return;

	}

This is realy frustrating. hahaha I have the feeling I am near, but dont know what I’m doing wrong

I think you are moving a bit further away in the latest code, the previous code you posted was closer. Here’s a few things I picked up:

The way you have your query now: WHERE parent_id IS NULL is only ever going to give you the top-level items. Can you see why?

To get around the issue with NULL’s in your SQL query that @rpkamp pointed out, you can do what I suggested a couple of posts up and use MySQL’s NULL-safe equals operator(<=>) instead of the normal equals operator (=). So you would use:
WHERE parent_id <=> ?.

Next, what happened to $parent_id parameter? It’s fine that you’ve now added the $language_abbr parameter, but you still also need to have the $parent_idparameter.
Where you recursively call the function you still pass the id: $this->menu_items($menu_item['id']); as the first parameter. But the parameter is now $language_abbr, which doesn’t make any sense.
So change your method signature to include both parameters:

public function menu_items($language_abbr, $parent_id)

And pass values for both when you do the call:

$subs = $this->menu_items($language_abbr, $menu_item['id']);

Lastly, notice how the name of your function and the name of the variable after the as in your foreach is both called menu_items not only is this very confusing, it can cause nasty, hard to debug errors, so rather rename your function to something like build_menu_items. It’s generally a good idea to use verbs for function names anyway.

@cryptichorizon. Thanks again for the reply. Fair enough all you mentioned. I made the changes you suggested and brought it back to the earlier version:

	public function build_menu_items($language_abbr, $parent_id)
	{
		$sql = "SELECT `id`
					 , `label`
		             , `title`
					 , `item_class`
					 , `link_class`
					 , `url`
					 , `parent_id`
					 , `lang`
				  FROM `site_navigation` 
				 WHERE `lang` = ?
				   AND `parent_id` <=> ?";
				   
        $stmt 		= 	$this->pdo->prepare($sql);
		$stmt->execute(array($language_abbr, $parent_id));
		$results	=	$stmt->fetchAll(PDO::FETCH_ASSOC);
		
		$return		=	'';
		foreach ($results as $menu_item){
			$return .= '<li class="'.$menu_item['item_class'].'">';
				$return .= '<a class="' . $menu_item['link_class'] . '" title="' . $menu_item['title'] . '" href="' . $menu_item['url'] . '">' . $menu_item['label'] . '</a>';
				$subs = $this->build_menu_items($language_abbr, $menu_item['id']);
				
				if ( is_null($menu_item['parent_id'])) {
					$return .=	$subs;	
				} else {
					$return	.=  '<div class="dropdown-menu">';
						$return .=	$subs;
					$return .=	'</div>';	
				}
			$return .= '</li>';		
		}
		return $return;		
	}

only this part doesn’t seem right:

if ( is_null($menu_item['parent_id'])) {
	$return .=	$subs;	
}

That gives me the dropdown items where it should give me the items with parent_id = 0. Besides of that do I have an aditional question. I call the function in the Controller the following way:

'menu'	=> $this->page->build_menu_items($this->lang)

How do I declare the $parent_id in the Controller? Just to test it for now I added 2 !

'menu'	=> $this->page->build_menu_items($this->lang, 2)

That’s why I know that the above if statement is returning the sub items

You are really, really close now. I think both your questions are a symptom of the same issue.

So this call:

'menu'	=> $this->page->build_menu_items($this->lang, 2);


asks the function to start building the menu tree starting with all the items who’s parent is item id 2.

So if instead, you want it to start building the menu tree with all items that have a NULL parent_id, you need to change the call to


'menu'	=> $this->page->build_menu_items($this->lang, ______ );

? (Fill in the blank)

1 Like