QRdvark.com

Simple Mysql PHP Menu

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