How to build unlimited level of menu through PHP and mysql

Question

Well, to build my menu my menu I use a db similar structure like this

  2  Services                  0
  3  Photo Gallery             0
  4  Home                      0
  5  Feedback                  0
  6  FAQs                      0
  7  News & Events             0
  8  Testimonials              0
 81  FACN                      0
 83  Organisation Structure   81
 84  Constitution             81
 85  Council                  81
 86  IFAWPCA                  81
 87  Services                 81
 88  Publications             81

To assign another submenu for existing submenu I simply assign its parent's id as its value of parent field. parent 0 means top menu

now there is not problem while creating submenu inside another submenu

now this is way I fetch the submenu for the top menu

<ul class="topmenu">
    <? $list = $obj -> childmenu($parentid); 
        //this list contains the array of submenu under $parendid
        foreach($list as $menu) {
            extract($menu);
            echo '<li><a href="#">'.$name.'</a></li>';
        }
    ?>
</ul>

What I want to do is.

I want to check if a new menu has other child menu

and I want to keep on checking until it searches every child menu that is available

and I want to display its child menu inside its particular list item like this

<ul>       
       <li><a href="#">Home</a>
        <ul class="submenu">
           ........ <!-- Its sub menu -->
           </ul>
       </li>
</ul>

score 22 · Answer 1 · edited Jan 30 '12 at 18:53

Here is a "developer-friendly" version of the "one query, no recursion" solution for this problem.

SQL:

SELECT id, parent_id, title, link, position FROM menu_item ORDER BY parent_id, position;

PHP:

$html = '';
$parent = 0;
$parent_stack = array();

// $items contains the results of the SQL query
$children = array();
foreach ( $items as $item )
    $children[$item['parent_id']][] = $item;

while ( ( $option = each( $children[$parent] ) ) || ( $parent > 0 ) )
{
    if ( !empty( $option ) )
    {
        // 1) The item contains children:
        // store current parent in the stack, and update current parent
        if ( !empty( $children[$option['value']['id']] ) )
        {
            $html .= '<li>' . $option['value']['title'] . '</li>';
            $html .= '<ul>'; 
            array_push( $parent_stack, $parent );
            $parent = $option['value']['id'];
        }
        // 2) The item does not contain children
        else
            $html .= '<li>' . $option['value']['title'] . '</li>';
    }
    // 3) Current parent has no more children:
    // jump back to the previous menu level
    else
    {
        $html .= '</ul>';
        $parent = array_pop( $parent_stack );
    }
}

// At this point, the HTML is already built
echo $html;

You just need to understand the usage of the $parent_stack variable.

It is a "LIFO" stack (Last In, First Out) - the image in the Wikipedia article worths a thousand words: http://en.wikipedia.org/wiki/LIFO_%28computing%29

When a menu option has sub-options, we store its parent ID in the stack:

array_push( $parent_stack, $parent );

And then, we immediately update $parent, making it be the current menu option ID:

$parent = $option['value']['id'];

After we looped all its sub-options, we can return back to the previous level:

$parent = array_pop( $parent_stack );

This is why we stored the parent ID in the stack!

My suggestion is: contemplate the code snippet above, and understand it.

Questions are welcome!

One of the advantages I see in this approach is that it eliminates the risk of entering into an infinite loop, which can happen when recursion is used.

@J.Bruni What is `['value']` in `$option['value']['id']`? In sql query does not see `['value']`. Where it is defined? — Andris, Jul 03 '15 at 12:19
@user2118559 - the PHP function `each` returns an array containing the `value` key - see http://php.net/manual/en/function.each.php — J. Bruni, Jul 06 '15 at 22:12
Very good code, thanks! Seems inside `if ( !empty( $children[$option['value']['id']] ) )` must remove ending `li`? Like `$html .= '
@user2118559 - not necessarily; is is fine to close the list-item (`li`), because it will also open a nested unordered list (`ul`) — J. Bruni, Jul 22 '15 at 17:54

score 17 · Answer 2 · edited Apr 04 '11 at 13:55

17

With a database structure like yours, it is possible to build the whole HTML menu with a single query and without recursion.

Yes - I will repeat:

ONE QUERY
NO RECURSION

This is the approach I always use myself.

Pasted the code here - fully functional:

http://pastebin.com/GAFvSew4

Jump to line 67 to see the interesting part ("get_menu_html").

The main loop starts at line 85.

There are five "customizable" HTML snippets:

menu wrapper opening (line 83)
menu wrapper closing (line 122)
menu item with childs opening (line 100)
menu item with childs closing (line 92)
menu item without childs (line 113)

(The code could be cleaner if I hadn't worried with tabulation.)

SQL to create and populate sample database is available at the end of the script.

You can try and let us know your thoughts.

edited Apr 04 '11 at 13:55

Starx

77,474
47
185
261

answered Jul 30 '10 at 03:58

J. Bruni

20,322
12
75
92

nice one J. Bruni, real sleek. however I do believe that's a lot of coding than just using the plain recursion – Starx Jul 30 '10 at 13:02
@Starx: Thanks. But you got the wrong impression, because the code I linked is not merely a snippet, but a "fully featured", working solution (OOP, connects database, etc). I will add another answer, containing a "user-friendly" snippet. (A simplified version, easier to understand, where I keep the important "core" and remove the "extras".) – J. Bruni Aug 01 '10 at 00:13
1

great class, perfect solution – enapupe Sep 20 '13 at 18:46
@J.Bruni good class. i changed your mysql_* functions to PDO. And now looks like better. I have a question. after 4 Years :) how can we add new `css classes` `2nd` and `3rd` level menus? – HddnTHA Sep 09 '14 at 17:35
1

@HddnTHA (3 months later...) use `count( $parent_stack )` to find out how deep in the nesting you are, and select or build a CSS class name based on it. – J. Bruni Dec 06 '14 at 19:19
@J.Bruni, just wanted to say - THANKS. Great script! – sinisake Aug 19 '15 at 12:05

score 11 · Accepted Answer · answered May 20 '10 at 07:55

11

You need to use recursive functions for this. Technically, there's a few ways to do it, but recursion is really the best option here.

Here's the basic gist of how it would work:

function drawMenu ($listOfItems) {
    echo "<ul>";
    foreach ($listOfItems as $item) {
        echo "<li>" . $item->name;
        if ($item->hasChildren()) {
            drawMenu($item->getChildren()); // here is the recursion
        }
        echo "</li>";
    }
    echo "</ul>";
}

The properties and methods of $item are just examples, and I'll leave it up to you to implement these however you need to, but I think it gets the message across.

answered May 20 '10 at 07:55

nickf

537,072
198
649
721

3

While your approach is valid, this amount of recursion may result in a very large number of database hits (perhaps one for each call to hasChildren, and another for getChildren). If these were items stored in memory, no problem, but to hit the database each time is going to slow things down. – Alistair Evans May 20 '10 at 07:57
2

@Kazar - well that depends entirely on the implementation. You could do it all in one query and then parse the results into a tree, or look at using Nested Sets http://dev.mysql.com/tech-resources/articles/hierarchical-data.html - though, since this is a menu, it's unlikely to have that many levels of recursion that it's worth worrying about. – nickf May 20 '10 at 13:30

score 5 · Answer 4 · answered May 20 '10 at 07:56

I would suggest that you look into pre-ordered tree traversal. There is an article on the issue at:

Managing Hierarchical Data in MySQL

Effectively, you take each page as a 'node'. Each node has a reference to it's parent. When you change the layout of the nodes (add a child, move nodes, etc), you recalculate a 'left' and 'right' value for each node (the article above explains this in great detail, with links to source code in php). What you end up with is the ability to very quickly determine if a given node is a direct or indirect child of any other node, as well as get all the child nodes of a given node.

Jon Black · Answer 5 · 2011-03-30T06:47:28.923

alt text http://i.imagehost.org/0934/product_hier.jpg http://pastie.org/969286

drop table if exists product;

create table product
(
prod_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent_id smallint unsigned null,
key (parent_id)
)engine = innodb;


insert into product (name, parent_id) values
('Products',null), 
   ('Systems & Bundles',1), 
   ('Components',1), 
      ('Processors',3), 
      ('Motherboards',3), 
        ('AMD',5), 
        ('Intel',5), 
           ('Intel LGA1366',7);


delimiter ;

drop procedure if exists product_hier;

delimiter #

create procedure product_hier
(
in p_prod_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_id smallint unsigned, 
 prod_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_id, prod_id, v_depth from product where prod_id = p_prod_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from product p inner join hier on p.parent_id = hier.prod_id and hier.depth = v_depth) then

        insert into hier 
            select p.parent_id, p.prod_id,  v_depth + 1 from product p 
            inner join tmp on p.parent_id = tmp.prod_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 p.prod_id,
 p.name as prod_name,
 b.prod_id as parent_prod_id,
 b.name as parent_prod_name,
 hier.depth
from 
 hier
inner join product p on hier.prod_id = p.prod_id
inner join product b on hier.parent_id = b.prod_id
order by
 hier.depth, hier.prod_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;


call product_hier(3);

call product_hier(5);

score 1 · Answer 6 · answered Jun 19 '13 at 20:30

http://pastebin.com/ariBn3pE

You need to use recursion, but my approach it's different, I created a class to handle each menu individually, then queried for results and group each elements in their individual object according to their parents, organized that by levels, and then merge all the objects into one... check the pastebin for the full code

score 0 · Answer 7 · answered May 20 '10 at 07:56

i would use a recursive function.

i know this isn't exactly like your code, but I think you can get the general concept if you understand recursion. if you don't understand recursion check out http://en.wikipedia.org/wiki/Recursion_(computer_science)

$list = new List();

function print_menu($list) {

    echo '<ul>';
    foreach($list as $item) {
        echo '<li><a href="#">' . $item->name . '</a>';
        if($item->has_child) {
            print_menu($item);
        }
        echo '</li>';
    }
    echo '</ul>';
}

score 0 · Answer 8 · edited Jun 10 '16 at 12:20

I found this way, working with Yii Framework.

$children = array();

foreach($model as $k => $item){
    if(empty($item->cn_id_menu_padre))
        $children[$item->cn_id] = $item->attributes;
    else
        $children[$item->cn_id_menu_padre]['hijos'][] = $item->attributes;
}

foreach($children as $k=>$child){
    if(array_key_exists('hijos',$child))
    {
        echo 'li y dentro ul<br>';
        foreach($child['hijos'] as $hijo){
            echo 'li<br>';
        }
    }
    else
        echo 'li<br>';
}

In case that you need one more level, you could make another level in children array like hijos_de_hijos and do the comparison then in the if statement.

Oh, of course, to compare if cn_id_menu_padre is empty, the value in the database should be null.

How to build unlimited level of menu through PHP and mysql

8 Answers8

Linked