Simple Mysql PHP Menu

There are many ways in which a menu, and menu structure can be created. Here we show how to create simple, multiple level menu system with just a single database table.

• Create the Tables

Create the Tables

The table structure is quite simple, and key to manipulating the data is in the parent child relationship created with the menu_item_id and the menu_parent_id.

CREATE table menu_items(
	menu_item_id int not null auto_increment,
	menu_item_name tinytext,
	menu_description tinytext,
	menu_url text,
	menu_parent_id int not null default 0,
	PRIMARY KEY(menu_item_id)
) ENGINE=INNODB;

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(1, 'Cars', 'Stuff about cars', '#', 0);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(2, 'Sedans', 'Sedan cars', '#', 1);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(3, 'Station Wagons', 'Station Wagon cars', '#', 1);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(4, 'Utes', 'You Beaut', '#', 1);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(5, 'Big Utes', 'You Beaut', '#', 4);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(6, 'Little Utes', 'You Beaut', '#', 4);


INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(7, 'Trucks', 'Stuff about Trucks', '#', 0);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(8, 'Tip Trucks', 'Tippers', '#', 7);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(9, 'Lorries', 'Bigger trucks', '#', 8);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(10, 'Road Trains', 'LOOONG Trucks', '#', 8);


INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(11, 'Boats', 'LOOONG Trucks', '#', 0);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(12, 'Yachts', 'Sail Boats', '#', 11);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(13, 'Power Boats', 'Fast Boats', '#', 11);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(14, 'Dinghy', 'Small Power Boats', '#', 13);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(15, 'Half Cabin', 'Bigger Boats', '#', 13);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(16, 'Full Cabin', 'Even Bigger Boats', '#', 13);

INSERT INTO menu_items
(menu_item_id, menu_item_name, menu_description, menu_url, menu_parent_id)
VALUES
(17, 'Cruisers', 'Really Big Boats', '#', 13);

The Code

With the database in place, the code to fetch the menu is rather simple and relies on references to do the hard work.


<?php

    // connect to mysql
    $link = mysql_connect( 'localhost', 'username', 'password' );

    // select the database
    mysql_select_db('my_database', $link);


    // create an array to hold the references
    $refs = array();

    // create and array to hold the list
    $list = array();

    // the query to fetch the menu data
    $sql = "SELECT menu_item_id, menu_parent_id, menu_item_name FROM menu_items ORDER BY menu_item_name";

    // get the results of the query
    $result = mysql_query($sql);

    // loop over the results
    while($data = @mysql_fetch_assoc($result))
    {
        // Assign by reference
        $thisref = &$refs[ $data['menu_item_id'] ];

        // add the the menu parent
        $thisref['menu_parent_id'] = $data['menu_parent_id'];
        $thisref['menu_item_name'] = $data['menu_item_name'];

        // if there is no parent id
        if ($data['menu_parent_id'] == 0)
        {
            $list[ $data['menu_item_id'] ] = &$thisref;
        }
        else
        {
            $refs[ $data['menu_parent_id'] ]['children'][ $data['menu_item_id'] ] = &$thisref;
        }
    }

    /**
    *
    * Create a HTML list from an array
    *
    * @param    array    $arr
    * @param    string    $list_type
    * @return    string
    *
    */
    function create_list( $arr )
    {
        $html = "\n<ul>\n";
        foreach ($arr as $key=>$v) 
        {
            $html .= '<li>'.$v['menu_item_name']."</li>\n";
            if (array_key_exists('children', $v))
            {
                $html .= "<li>";
                $html .= create_list($v['children']);
                $html .= "</li>\n";
            }
            else{}
        }
        $html .= "</ul>\n";
        return $html;
    }

    echo create_list( $list );

?>

Demonstration

Boats


Power Boats


Cruisers
Dinghy
Full Cabin
Half Cabin



Yachts


Cars


Sedans
Station Wagons
Utes



Big Utes
Little Utes




Trucks


Tip Trucks



Lorries
Road Trains