How to build unlimited level of menu through PHP and mysql

by

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.

More Related Contents:

  1. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in? [duplicate]
  2. regarding installation of php in windows
  3. save user images in MYSQL database by which method? [closed]
  4. Im trying to create some temporary tables in mysql using php
  5. How to apply bindValue method in LIMIT clause?
  6. Best way to INSERT many values in mysqli?
  7. Importing CSV data using PHP/MySQL
  8. Convert latin1 characters on a UTF8 table into UTF8
  9. php/mysql with multiple queries
  10. Notice: Array to string conversion in
  11. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  12. Find total number of results in mySQL query with offset+limit
  13. mysql_fetch_array() expects parameter 1 to be resource problem [duplicate]
  14. PDO multiple queries
  15. Using strtotime for dates before 1970
  16. How to efficiently find the closest locations nearby a given location
  17. Turn database result into array
  18. How to set up entity (doctrine) for database view in Symfony 2
  19. Update query with PDO and MySQL
  20. (mysql, php) How to get auto_increment field value before inserting data?
  21. Trying to get property of non-object – CodeIgniter
  22. What is the type this string? a:1:{s:2:”en”;}
  23. PHP to store images in MySQL or not?
  24. UTF-8 character encoding battles json_encode() [duplicate]
  25. How can I pass an array of PDO parameters yet still specify their types?
  26. MySQL & PHP Parameter 1 as Resource
  27. MySQL GROUP BY and Fill Empty Rows
  28. mysql injection damages?
  29. how to execute mysql command DELIMITER
  30. PDO and MySQL ‘between’

Leave a Comment