Display a hierarchical menu using php

[oddz]

Try this out and post the results.

<?php
 
include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
   
$rows = array();
$sql = 'SELECT id, parent_id, name FROM pages';
$result = mysqli_query($link, $sql);
    if (!$result)
    {
        $error = 'Error fetching page structure, for nav menu generation.';
        include 'error.html.php';
        exit();
    }
 
while($row = mysqli_fetch_assoc($result) ) {

    if( strcasecmp($row['parent_id'],'null') === 0 || empty($row['parent_id']) ) {
         $row['parent_id'] = null;
    }

    $rows[] = $row;
}
 
// echo '<pre>',var_dump($rows),'</pre>'; 
 
// covert raw result set to tree
$menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
echo '<pre>',print_r($menu),'</pre>'; 

[atlr]

Aaah
Looking like it’s taking shape

I’ve now got a raw array output at the top, and a formatted version at the bottom.

Do I take it that if I comment-out the line that outputs the raw array, so the code looks like this (pasted below), that’ll be all the mods I need to make?

include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';

$rows = array();
$sql = 'SELECT id, parent_id, name FROM pages';
$result = mysqli_query($link, $sql);
    if (!$result)
    {
        $error = 'Error fetching page structure, for nav menu generation.';
        include 'error.html.php';
        exit();
    }

while($row = mysqli_fetch_assoc($result) ) {

    if( strcasecmp($row['parent_id'],'null') === 0 || empty($row['parent_id']) ) {
         $row['parent_id'] = null;
    }

    $rows[] = $row;
}

// echo '<pre>',var_dump($rows),'</pre>';

// covert raw result set to tree
$menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
//echo '<pre>',print_r($menu),'</pre>';

// display menu
echo themeMenu($menu,1);

[oddz]

Does this give you the expected result?

include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
   
$rows = array();
$sql = 'SELECT id, parent_id, name FROM pages';
$result = mysqli_query($link, $sql);
    if (!$result)
    {
        $error = 'Error fetching page structure, for nav menu generation.';
        include 'error.html.php';
        exit();
    }
 
while($row = mysqli_fetch_assoc($result) ) {

    if( strcasecmp($row['parent_id'],'null') === 0 || empty($row['parent_id']) ) {
         $row['parent_id'] = null;
    }

    $rows[] = $row;
}
 
// echo '<pre>',print_r($rows),'</pre>'; 
 
// covert raw result set to tree
$menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
// echo '<pre>',print_r($menu),'</pre>';
 
// display menu
echo themeMenu($menu,1);
 
/*
* ------------------------------------------------------------------------------------
* Utility functions
* ------------------------------------------------------------------------------------
*/
 
/*
* Convert adjacency list to hierarchical tree
*
* @param value of root level parent most likely null or 0
* @param array result
* @param str name of primary key column
* @param str name of parent_id column - most likely parent_id
* @param str name of index that children will reside ie. children, etc
* @return array tree
*/
function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
           
    $arrChildren = array();
           
    for($i=0;$i<count($arrRows);$i++) {
        if($intParentId === $arrRows[$i][$strParentsIdField]) {
            $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
        }
    }
               
    $intChildren = count($arrChildren);
    if($intChildren != 0) {
        for($i=0;$i<$intChildren;$i++) {
            $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
        }       
    }
               
    return $arrChildren;
       
}
 
/*
* Theme menu
*
* @param array menu
* @param runner (depth)
* @return str themed menu
*/
function themeMenu($menu,$runner) {
 
    $out = '';
       
    if(empty($menu)) {
        return $out;
    }
       
    $out.='<ul>';
    foreach($menu as $link) {
        $out.= sprintf(
            '<li class="depth-%u">%s%s</li>'
            ,$runner
            ,$link['name']
            ,themeMenu($link['links'],($runner+1))
        );
    }
   
    $out.='</ul>';
    return $out;
   
}

[oddz]

If that does yield the expected result you should really consider not using an empty string or null for the top level - either or. The hack I threw in there to convert an empty string to null really shouldn’t be necessary. Also, I think it may be a MySQLi thing but on my machine with mysql and pdo null columns are properly converted to a true null, rather than null inside a string, which will obviously fail with a strict comparison. Not sure why I am getting a true null value and your getting string unless your storing null in a string actually, which I doubt.

[atlr]

@oddz

If that does yield the expected result you should really consider not using an empty string or null for the top level - either or. The hack I threw in there to convert an empty string to null really shouldn’t be necessary. Also, I think it may be a MySQLi thing but on my machine with mysql and pdo null columns are properly converted to a true null, rather than null inside a string, which will obviously fail with a strict comparison. Not sure why I am getting a true null value and your getting string unless your storing null in a string actually, which I doubt.

Hey Oddz.

Thanks for this. I’ve taken a look at the DB, and “NULL” is stored in capitals in the top-level pages’ parent_id fields.

Does that help?

Al

[oddz]

NULL should be a true NULL value, not stored as a string with a null. A string with NULL in it is not actually NULL. That said all root level items should either use a true NULL or 0 value depending on if there is some type of unique key constraint. If parent_id is part of a unique key constraint than 0 would be more appropriate than null since null would result in the constraint not being satisfied by root level items.

[r937]

0 is not an appropriate value for the top level’s parent

why would there be a UNIQUE key on that column???

use NULL, not 0

[oddz]

A common case is unique name per parent. In which case there would need to be a unique key on parent_id and name. Using a NULL parent_id for the root level than would result in those items not following the restraint.

[StarLion]

If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.

You’re making an assumption about the user’s natural key that should not be made; the natural key might include template_id, for example.

[atlr]

@StarLion

If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.

You’re making an assumption about the user’s natural key that should not be made; the natural key might include template_id, for example.

There is a template_id, yes. As to the rest of what you guys have said, I have to admit to not exactly following what the problem/argument/different method is that’s being discussed for using one method over another?

@Oddz - In the final bit of code that I’ve now got, how could I specify different classes for each item: child, grand-child, great grand-child, etc, so I can apply different css classes to these, for use in an on-hover drop-down menu.

Al

[StarLion]

Oddz’ code already gives you that setup:
‘<li class=“depth-%u”>%s%s</li>’
Which will give you the CSS classes depth-1, depth-2, etc etc…

(Note that %u is being filled by $runner , and is not itself a meaningful ‘variable’)

[r937]

@oddz

A common case is unique name per parent.

that doesn’t make sense to me

maybe you could actually whip up an example of what you mean?

the only result i can see of putting a unique constraint on parent_id is to ensure there is maximum one child per parent

which is pretty unusual, wouldn’t you say?

[atlr]

@StarLion

Oddz’ code already gives you that setup:
‘<li class=“depth-%u”>%s%s</li>’
Which will give you the CSS classes depth-1, depth-2, etc etc…

(Note that %u is being filled by $runner , and is not itself a meaningful ‘variable’)

Aah, great - yeah, I saw that in the source, but was slightly unsure as to what &u was! Upon reading-up on sprintf, I see that this is populated by the runner - am I right?

Are there any sitepoint books, do you think, that I could read in order to learn more about how to construct a script such as this on my own, in the future? Very keen to learn more!

The problem I now have is that the root items (ultimate parents), of which there are six, have image-based rollovers. I suppose I somehow need to insert an if() statement into the '<li class=“depth-%u” part that will look to include a reference to each parent page’s name, if it exists in the database (i’d need to create a new column, which isn’t a problem), and then add that somewhere to the class name, that I can then hard-code in the css.

What do you think?

Al

[StarLion]

I think what oddz has given you is the basis for what you want to do. Note the recursiveness (function that calls itself) of the TheMenu function, and extrapolate his output into whatever you want it to do.

[atlr]

@StarLion

I think what oddz has given you is the basis for what you want to do. Note the recursiveness (function that calls itself) of the TheMenu function, and extrapolate his output into whatever you want it to do.

So if I was to put, for instance:

1. an extra call into the SELECT function, so it read like

$sql = ‘SELECT id, parent_id, link_title, link_class FROM pages’;

2. then change the $menu var to read:

$menu = convertAdjacencyListToTree(null,$rows,‘id’,‘parent_id’,‘links’,‘class’);

3. Then change the TheMenu function to read:

function themeMenu($menu,$runner) {
 
    $out = '';
        
    if(empty($menu)) {
        return $out;
    }
        
    $out.='<ul>';
    foreach($menu as $link) {
        $out.= sprintf(
            '<li class="depth-%u-$link['class']">%s%s</li>'
            ,$runner
            ,$link['link_title']
            ,themeMenu($link['links'],($runner+1))
        );
    }
    
    $out.='</ul>';
    return $out;
    
}

Would that work, or have I missed something here?

AL

[oddz]

1.) step one is correct
2.) step two is not needed
3.) step three would be better suited like

function themeMenu($menu,$runner) {
 
    $out = '';
        
    if(empty($menu)) {
        return $out;
    }
        
    $out.='<ul>';
    foreach($menu as $link) {
        $out.= sprintf(
            '<li class="depth-%u%s">%s%s</li>'
            ,$runner
            ,isset($link['link_class'])?$link['link_class']:''
            ,$link['link_title']
            ,themeMenu($link['links'],($runner+1))
        );
    }
    
    $out.='</ul>';
    return $out;
    
} 

[oddz]

@StarLion

If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.

You’re making an assumption about the user’s natural key that should not be made; the natural key might include template_id, for example.

CREATE TABLE test(
     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
    ,parent_id SMALLINT UNSIGNED NULL
    ,item_name VARCHAR(128) NOT NULL
    ,PRIMARY KEY(id)
    ,UNIQUE KEY(parent_id,item_name)
);

INSERT INTO test (parent_id,item_name) VALUES (NULL,'test'),(NULL,'test'),(NULL,'test'),(NULL,'test');

Notice: the insert query does not fail and you end up with 4 items named test with the same parent because of the NULL value.

INSERT INTO test (parent_id,item_name) VALUES (0,'test'),(0,'test');

Notice: That only inserts one, as the other is a duplicate.

That is what I meant when I referred to having something being unique per parent. NULL does not obey the unique key. So anywhere a NULL column is part of a unique key means the unique key will not be obeyed for NULL.

Which is actually pretty nifty for handling soft-deletes:

CREATE TABLE blogs (
       id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
       ,title VARCHAR(128) NOT NULL
       ,deleted TINYINT NULL DEFAULT '0'
       ,PRIMARY KEY(id)
      ,UNIQUE KEY(title,deleted)
);

insert blog:

INSERT INTO blogs (title) VALUES ('blog1');

• fail - duplicate key

INSERT INTO blogs (title) VALUES ('blog1');

• soft delete

UPDATE blogs SET deleted = NULL WHERE title = 'blog1';

• success

INSERT INTO blogs (title) VALUES ('blog1');

[atlr]

@oddz

1.) step one is correct
2.) step two is not needed
3.) step three would be better suited like

function themeMenu($menu,$runner) {
 
    $out = '';
        
    if(empty($menu)) {
        return $out;
    }
        
    $out.='<ul>';
    foreach($menu as $link) {
        $out.= sprintf(
            '<li class="depth-%u%s">%s%s</li>'
            ,$runner
            ,isset($link['link_class'])?$link['link_class']:''
            ,$link['link_title']
            ,themeMenu($link['links'],($runner+1))
        );
    }
    
    $out.='</ul>';
    return $out;
    
} 

Thanks for this oddz. I’m not quite sure where the %u and %s are defined in the first place? Do you know of any books or learning resources (preferably sitepoint!) that I can get, to read-up on all this a little more?

One query with the above - what if I wanted to give each UL a class too?

Cheers

Al

[StarLion]

@oddz

CREATE TABLE test(
     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
    ,parent_id SMALLINT UNSIGNED NULL
    ,item_name VARCHAR(128) NOT NULL
    ,PRIMARY KEY(id)
    ,UNIQUE KEY(parent_id,item_name)
);

INSERT INTO test (parent_id,item_name) VALUES (NULL,'test'),(NULL,'test'),(NULL,'test'),(NULL,'test');

Notice: the insert query does not fail and you end up with 4 items named test with the same parent because of the NULL value.

Interesting. Completely illogical, but that’s indeed what happens. Bad engine coding, imo.

That is what I meant when I referred to having something being unique per parent. NULL does not obey the unique key. So anywhere a NULL column is part of a unique key means the unique key will not be obeyed for NULL.

Still not relevant, though - again, you’re making an assumption about the user’s desires for the table.

One query with the above - what if I wanted to give each UL a class too?

    $out.='<ul>'; 

(not sure why that . is there, but anyway)

    $out ='<ul class="ul-'.$runner.'">'; 

[oddz]

@StarLion

Still not relevant, though - again, you’re making an assumption about the user’s desires for the table.

No assumption was made, I was merely providing some information that seemed relevant to using 0 or NULL as a parent. I have no idea what the actual table looks like considering the user never posted the create table statement. For all I know every column could be part of a unique key. Also, the $runner is there to identify the depth, which in most cases is a useful “hook” for CSS to have.

@StarLion

(not sure why that . is there, but anyway)

By the way… thanks for pointing out that gigantic inefficiency issue – much appreciated.

The reason it was there though is because I pulled that function from this class where it makes more sense.

<?php
namespace UI\\Element\\Common\\Listing;

class Tree implements \\UI\\Element {
	
	public function settings() {
		return array(
			'data'=>array(
				'default'=>array()
			)
			,'child_key'=>array(
				'default'=>'children'
			)
			,'value_key'=>array(
				'default'=>'value'
			)
			,'list_element'=>array(
				'default'=>'ul'
			)
			,'depth_class'=>array(
				'default'=>true
			)
			,'mutation'=>array(
				'default'=>null
			)
			,'form'=>array( // flag to wrap contents in form element
				'default'=>false
			)
			,'form_action'=>array( // form action, when form
				'default'=>''
			)
			,'form_name'=>array( // form name, when form
				'default'=>''
			)
			,'form_method'=>array( // form action, when form
				'default'=>''
			)
			,'form_legend'=>array( // form legend, when form
				'default'=>''
			)
		);
	}
	
	public function html($settings,\\UI\\Manager $ui) {
		return $this->_html($settings);
	}
	
	private function _html($settings,$runner=0) {
		
		extract($settings);
		$out = '';
		
		if($form === true) {
			$out.= sprintf(
				'<form name="%s" action="%s" method="%s"><fieldset><legend>%s</legend>'
				,$form_name
				,$form_action
				,$form_method
				,$form_legend
			);
		}
		
		/*
		* Build out tree 
		*/
		if(!empty($data)) {
			$out.="<$list_element>";
			foreach($data as $index=>$item) {
				$out.= sprintf(
					'<li%s>%s%s</li>'
					,$depth_class === true?' class="depth-'.$runner.'"':''
					,$mutation !== null?call_user_func_array($mutation,array($item[$value_key],$item,$index)):$item[$value_key]
					,$this->_html(array(
						'data'=>isset($item[$child_key]) && !empty($item[$child_key])?$item[$child_key]:array()
						,'child_key'=>$child_key
						,'value_key'=>$value_key
						,'list_element'=>$list_element
						,'depth_class'=>$depth_class
						,'mutation'=>$mutation
					),($runner+1))
				);
			}
			$out.="</$list_element>";
		}
		
		if($form === true) {
			$out.= '</fieldset></form>';
		}
		
		return $out;
		
	}
	
}
?>

By which I use to build out trees.

echo $this->ui('Common.Listing.Tree',array(
	'data'=>$terms
	,'value_key'=>'human_name'
	,'child_key'=>'terms'
	,'list_element'=>'ul'
	,'mutation'=>$mutation
	,'form'=>true
	,'form_legend'=>$vocabulary?$vocabulary['human_name']:''
	,'form_action'=>$frm_action
	,'form_method'=>$frm_method
	,'form_name'=>$frm_name
));