PHP - Generate menu from database table

Question

I have this code from J Bruni which I found in another SO post. However my database structure is a little different and I need to get this code to work with my table structure.

The original code was meant to work with this structure:

/*** SAMPLE DATABASE: ***

CREATE TABLE `menu_item` (
  `id` int(11) NOT NULL,
  `title` varchar(75) DEFAULT NULL,
  `link` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (1,'1','1.html',0,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (2,'2','2.html',0,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (3,'11','11.html',1,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (4,'12','12.html',1,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (5,'21','21.html',2,1);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (6,'22','22.html',2,2);
INSERT INTO `menu_item` (`id`, `title`, `link`, `parent_id`, `position`) VALUES (7,'3','3.html',0,3);

*/

But my table structure is like this:

CREATE TABLE tbmenu (
  row_id      Integer(11) NOT NULL AUTO_INCREMENT,
  category_id Integer(11) NOT NULL,
  menuitem    NVarChar(128) COLLATE utf8_general_ci NOT NULL,
  pageurl     NVarChar(128) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (
      row_id
  )
) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

This is the original code for the original database structure:

<?php

/**
 * Generate HTML for multi-dimensional menu from MySQL database
 * with ONE QUERY and WITHOUT RECURSION 
 * @author J. Bruni
 */
class MenuBuilder
{
    /**
     * MySQL connection
     */
    var $conn;

    /**
     * Menu items
     */
    var $items = array();

    /**
     * HTML contents
     */
    var $html  = array();

    /**
     * Create MySQL connection
     */
    function MenuBuilder()
    {
        $this->conn = mysql_connect( 'localhost', 'user', 'pass' );
        mysql_select_db( 'example', $this->conn );
    }

    /**
     * Perform MySQL query and return all results
     */
    function fetch_assoc_all( $sql )
    {
        $result = mysql_query( $sql, $this->conn );

        if ( !$result )
            return false;

        $assoc_all = array();

        while( $fetch = mysql_fetch_assoc( $result ) )
            $assoc_all[] = $fetch;

        mysql_free_result( $result );

        return $assoc_all;
    }

    /**
     * Get all menu items from database
     */
    function get_menu_items()
    {
        // Change the field names and the table name in the query below to match tour needs
        $sql = 'SELECT id, parent_id, title, link, position FROM menu_item ORDER BY parent_id, position;';
        return $this->fetch_assoc_all( $sql );
    }

    /**
     * Build the HTML for the menu 
     */
    function get_menu_html( $root_id = 0 )
    {
        $this->html  = array();
        $this->items = $this->get_menu_items();

        foreach ( $this->items as $item )
            $children[$item['parent_id']][] = $item;

        // loop will be false if the root has no children (i.e., an empty menu!)
        $loop = !empty( $children[$root_id] );

        // initializing $parent as the root
        $parent = $root_id;
        $parent_stack = array();

        // HTML wrapper for the menu (open)
        $this->html[] = '<ul>';

        while ( $loop && ( ( $option = each( $children[$parent] ) ) || ( $parent > $root_id ) ) )
        {
            if ( $option === false )
            {
                $parent = array_pop( $parent_stack );

                // HTML for menu item containing childrens (close)
                $this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 ) . '</ul>';
                $this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ) . '</li>';
            }
            elseif ( !empty( $children[$option['value']['id']] ) )
            {
                $tab = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 );

                // HTML for menu item containing childrens (open)
                $this->html[] = sprintf(
                    '%1$s<li><a href="%2$s">%3$s</a>',
                    $tab,   // %1$s = tabulation
                    $option['value']['link'],   // %2$s = link (URL)
                    $option['value']['title']   // %3$s = title
                ); 
                $this->html[] = $tab . "\t" . '<ul class="submenu">';

                array_push( $parent_stack, $option['value']['parent_id'] );
                $parent = $option['value']['id'];
            }
            else
                // HTML for menu item with no children (aka "leaf") 
                $this->html[] = sprintf(
                    '%1$s<li><a href="%2$s">%3$s</a></li>',
                    str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ),   // %1$s = tabulation
                    $option['value']['link'],   // %2$s = link (URL)
                    $option['value']['title']   // %3$s = title
                );
        }

        // HTML wrapper for the menu (close)
        $this->html[] = '</ul>';

        return implode( "\r\n", $this->html );
    }
}

$menu = new MenuBuilder();
echo '<pre>' . htmlentities( $menu->get_menu_html() ) . '</pre>';



?>

..and this is the code modified for my database structure:

<?php

/**
 * Generate HTML for multi-dimensional menu from MySQL database
 * with ONE QUERY and WITHOUT RECURSION
 * @author J. Bruni
 */

     function is_iterable($var)
    {
      return $var !== null
        && (is_array($var)
            || $var instanceof Traversable
            || $var instanceof Iterator
            || $var instanceof IteratorAggregate
            );
    }

class MenuBuilder
{
    /**
     * MySQL connection
     */
    var $conn;

    /**
     * Menu items
     */
    var $items = array();

    /**
     * HTML contents
     */
    var $html  = array();

    /**
     * Create MySQL connection
     */
    function MenuBuilder()
    {
        $this->conn = mysqli_connect( "localhost", "rXXXt", "rXXXt" );
        mysqli_select_db( $this->conn , "XXXXdb" );
    }

    /**
     * Perform MySQL query and return all results
     */
    function fetch_assoc_all( $sql )
    {
        $result = mysqli_query( $this->conn, $sql  );

        if ( !$result )
            return false;

        $assoc_all = array();

        while( $fetch = mysqli_fetch_assoc( $result ) )
            $assoc_all[] = $fetch;

        mysqli_free_result( $result );

        return $assoc_all;
    }

    /**
     * Get all menu items from database
     */
    function get_menu_items()
    {
        // Change the field names and the table name in the query below to match tour needs
        $sql = "SELECT row_id, category_id, menuitem, pageurl FROM tbmenu ORDER BY row_id;";
        return $this->fetch_assoc_all( $sql );
    }

    /**
     * Build the HTML for the menu
     */
    function get_menu_html( $root_id = 0 )
    {
        $this->html  = array();
        $this->items = $this->get_menu_items();


        if (is_iterable($this->items))
        {
          foreach ( $this->items as $item )
            $children[$item['category_id']][] = $item;
        }
        // loop will be false if the root has no children (i.e., an empty menu!)
        $loop = !empty( $children[$root_id] );

        // initializing $parent as the root
        $parent = $root_id;
        $parent_stack = array();

        // HTML wrapper for the menu (open)
        $this->html[] = "<ul>";

        while ( $loop && ( ( $option = each( $children[$parent] ) ) || ( $parent > $root_id ) ) )
        {
            if ( $option === false )
            {
                $parent = array_pop( $parent_stack );

                // HTML for menu item containing childrens (close)
                $this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 ) . "</ul>";
                $this->html[] = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ) . "</li>";
            }
            elseif ( !empty( $children[$option['value']['row_id']] ) )
            {
                $tab = str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 );

                // HTML for menu item containing childrens (open)
                $this->html[] = sprintf(
                    '%1$s<li><a href="%2$s">%3$s</a>',
                    $tab,   // %1$s = tabulation
                    $option['value']['pageurl'],   // %2$s = link (URL)
                    $option['value']['menuitem']   // %3$s = title
                );
                $this->html[] = $tab . "\t" . '<ul class="submenu">';

                array_push( $parent_stack, $option['value']['category_id'] );
                $parent = $option['value']['row_id'];
            }
            else
                // HTML for menu item with no children (aka "leaf")
                $this->html[] = sprintf(
                    '%1$s<li><a href="%2$s">%3$s</a></li>',
                    str_repeat( "\t", ( count( $parent_stack ) + 1 ) * 2 - 1 ),   // %1$s = tabulation
                    $option['value']['pageurl'],   // %2$s = link (URL)
                    $option['value']['menuitem']   // %3$s = title
                );
        }

        // HTML wrapper for the menu (close)
        $this->html[] = "</ul>";

        return implode( "\r\n", $this->html );
    }
}

 $menu = new MenuBuilder();
 echo "<pre>" . $menu->get_menu_html() . "</pŕe>";

?>

Category_ID is 1, 2, 3, 4.. and each category has child menu items. Category names are in another table. So when displayed, it will display as a tree with each Category and then child menu items beneath.

I know that this question sounds like 'do my homework for me' but I spent a good 2 hours trying to modify the code to make it work, to no avail. I'm a little new to PHP's arrays and related syntax and so i find it difficult.

Thanks in advance!

sumit malik · Accepted Answer · 2015-04-27 10:06:07Z

1

if i am not wrong parent_id in original code is pointing to id of the same table, where as in your case it is id of some other table. In order to use the same code you have to modify the structure of your tables.

answered Apr 27, 2015 at 10:06

sumit malik

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter B · Accepted Answer · 2015-04-28 09:09:53Z

function topMenu() {
        global $MySQLi;
        $query = "SELECT * FROM `grp_menuitems` WHERE `hidden` = '0' AND `parent` = '0' ORDER BY `order`";
        $commit = $MySQLi->query($query);
        if($commit === false) {
            header("Location: error?code=01SET");
            die();
        }
        else
        {
            while($row = $commit->fetch_assoc()) {
                if($_SERVER['PHP_SELF'] == $row['link']) {
                    echo '<li class="current_page_item"><a href="'.$row['link'].'">'.$row['title'].'</a>';
                }
                else
                {
                    echo '<li><a href="'.$row['link'].'">'.$row['title'].'</a>';
                }
                $this->topMenuChildren($row['id']);
                echo '</li>';
            }
        }
    }

    function topMenuChildren($parent) {
        global $MySQLi;
        $query = "SELECT * FROM `grp_menuitems` WHERE `hidden` = '0' AND `parent` = '".$parent."' ORDER BY `order`";
        $commit = $MySQLi->query($query);
        if($commit === false) {
            header("Location: error?code=01SET");
            die();
        }
        else
        {
            if($commit->num_rows > 0) {
                echo '<ul>';
                while($row = $commit->fetch_assoc()) {
                    echo '<li><a href="'.$row['link'].'">'.$row['title'].'</a>';
                    $this->topMenuChildren($row['id']);
                    echo '</li>';
                }
                echo '</ul>';
            }
        }
    }

This is my function. I however use order rather than position as a column. Adapt as necessary. :)

Simply call topMenu(); and the rest is done via PHP. I know it's a bit different to your code structure, but you seem to have the knowledge to be able to analyze and get the idea.

Collectives™ on Stack Overflow

PHP - Generate menu from database table

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related