mpopp.net blog – Markus Popp

I’ve tried to create a database driven menu management. The menu items are stored in the database with an ID (menuitem) and a mother ID (motherid) which refers to its upper menu item or to 0, if it is a main menu item. Further we store the description, that’s the text that should be displayed in the menu and a filename, which we can use to store, where we want to link the menu item to. Finally we have a column itemsorder where we store, in which order the menu items should be displayed.

This table can store more than one separate menus, so we use the first column mainmenu to indicate, which menu we want to display.

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`mainmenu` int(10) unsigned NOT NULL,
`menuitem` int(10) unsigned NOT NULL,
`motherid` int(10) unsigned NOT NULL,
`description` varchar(100) NOT NULL,
`filename` varchar(100) NOT NULL,
`itemsorder` int(10) unsigned NOT NULL,
PRIMARY KEY  (`mainmenu`,`menuitem`),
KEY `motherid` (`motherid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `menu` VALUES (1, 1, 0, 'Menu 1', 'menu1', 10);
INSERT INTO `menu` VALUES (1, 2, 0, 'Menu 2', 'menu2', 20);
INSERT INTO `menu` VALUES (1, 3, 2, 'Menu 2/1', 'menu2_1', 30);
INSERT INTO `menu` VALUES (1, 4, 2, 'Menu 2/2', 'menu2_2', 40);
INSERT INTO `menu` VALUES (1, 5, 4, 'Menu 2/2/1', 'menu2_2_1', 50);
INSERT INTO `menu` VALUES (1, 6, 4, 'Menu 2/2/2', 'menu2_2_2', 60);
INSERT INTO `menu` VALUES (1, 7, 6, 'Menu 2/2/2/1', 'menu2_2_2_1', 70);
INSERT INTO `menu` VALUES (1, 8, 4, 'Menu 2/2/3', 'menu2_2_3', 80);
INSERT INTO `menu` VALUES (1, 9, 2, 'Menu 2/3', 'menu2_3', 90);
INSERT INTO `menu` VALUES (1, 10, 2, 'Menu 2/4', 'menu2_4', 100);
INSERT INTO `menu` VALUES (1, 11, 10, 'Menu 2/4/1', 'menu2_4_1', 110);
INSERT INTO `menu` VALUES (1, 12, 11, 'Menu 2/4/1/1', 'menu2_4_1_1', 120);
INSERT INTO `menu` VALUES (1, 13, 11, 'Menu 2/4/1/2', 'menu2_4_1_2', 130);
INSERT INTO `menu` VALUES (1, 14, 13, 'Menu 2/4/1/2/1', 'menu2_4_1_2_1', 140);
INSERT INTO `menu` VALUES (1, 15, 11, 'Menu 2/4/1/3', 'menu2_4_1_3', 150);
INSERT INTO `menu` VALUES (1, 16, 10, 'Menu 2/4/2', 'menu2_4_2', 160);
INSERT INTO `menu` VALUES (1, 17, 2, 'Menu 2/5', 'menu2_5', 170);
INSERT INTO `menu` VALUES (1, 18, 0, 'Menu 3', 'menu3', 180);
INSERT INTO `menu` VALUES (2, 1, 0, 'Menu 1', 'menu1', 10);
INSERT INTO `menu` VALUES (2, 2, 1, 'Menu 1/1', 'menu1_1', 20);
INSERT INTO `menu` VALUES (2, 3, 2, 'Menu 1/1/1', 'menu1_1_1', 30);
INSERT INTO `menu` VALUES (2, 4, 1, 'Menu 1/2', 'menu1_2', 40);
INSERT INTO `menu` VALUES (2, 5, 4, 'Menu 1/2/1', 'menu1_2_1', 50);
INSERT INTO `menu` VALUES (2, 6, 4, 'Menu 1/2/2', 'menu1_2_2', 60);
INSERT INTO `menu` VALUES (2, 7, 1, 'Menu 1/3', 'menu1_3', 70);
INSERT INTO `menu` VALUES (2, 8, 0, 'Menu 2', 'menu2', 80);
INSERT INTO `menu` VALUES (2, 9, 0, 'Menu 3', 'menu3', 90);
INSERT INTO `menu` VALUES (2, 10, 9, 'Menu 3/1', 'menu3_1', 100);

Up to now, MySQL users had to do quite a lot of work on the client side to get all the information that’s necessary to build the menu. We need to know at which level a menu item resides and which menu items should be displayed and which should not. This example should only make the top level items, the items at the same level as the active item and the items right below the active menu item visible.

One choice would be to store redundant data, but if we want to have good database design, we don’t get along without doing some extra work. I’ve tried to get this done by a stored procedure. It was not easy, because there are still some limits on SPs that require some tricky parts to pass by some troubles. Some of them were solved by temporary tables.

Here’s the code of the stored procedure called getMenu. It takes 2 parameters, first which menu should be used (column mainmenu) and the second, which menu item should be active. If the second parameter is set to 0, the complete menu will be displayed.

DELIMITER $$

DROP PROCEDURE IF EXISTS getMenu$$
CREATE PROCEDURE getMenu(IN p_menu INT, IN p_active INT)
BEGIN
 declare activeMotherId INT;
 declare countrec INT;
 declare counter INT default 1;

 drop temporary table if exists t1;

 create temporary table t1
   select menuitem,
       0 as level,
       'n' as display,
       '-' as haschildren,
       motherid,
       description,
       filename,
       itemsorder
     from menu
     where mainmenu = p_menu
     order by itemsorder;

 update t1 set level=1, display='y' where motherid = 0;

 update t1 set display='y' where menuitem = p_active;

 drop temporary table if exists t2;

 create temporary table t2 select * from t1;
 update t1 set haschildren='+' where (select count(*)
   from t2 where t1.menuitem=t2.motherid) > 0;

 drop temporary table if exists t2;

 set countrec = countrec - (select count(*)
   from t1 where motherid = 0);

 select count(*) from t1 into countrec;

 while countrec > 0 do
   drop temporary table if exists t2;

   create temporary table t2
     select menuitem from t1 where level = counter;

   update t1 set level = (counter + 1)
     where motherid in (select menuitem from t2);

   drop temporary table if exists t2;

   select count(*) from t1 where level=0 into countrec;

   set counter = counter + 1;
 end while;

 if p_active = 0 then
   update t1 set display='y';
 else

   select motherid from t1
     where menuitem = p_active into activeMotherId;

   update t1 set display='y'
     where motherid = activeMotherId or motherId = p_active;

   while activeMotherId != 0 do
     update t1 set display='y'
       where motherid = activeMotherId;

     select motherid
       from t1
       where menuitem = activeMotherId
       into activeMotherId;
   end while;
 end if;
 select * from t1;
 drop temporary table t1;
END$$

DELIMITER ;

First I move the relevant data to a temporary table called t1. This temporary table has 3 additional fields: level (which indicates the level of the menu item), display (‘y'(es)/’n'(o) – whether the menu item should be displayed or not) and haschildren (‘+’ – has child items, ‘-‘ – has no child items).

The following are some quite complex processes to get the right data into these new fields. Therefore I had to create 2 more temporary tables to store some data for a short amount of time. At the end, the result is being displayed with all data that’s required for comfortable processing in a PHP script like this:

<?php
ini_set('display_errors', 1);

$mysqli = 
   new mysqli('localhost', 'username', 'password', 'dbname');

if (mysqli_connect_error())
die ("Connection error: ".mysqli_connect_errno());

$activeId = isset($_GET['activeid']) ? $_GET['activeid'] : 1;

$sql = "call getMenu(1, ".$activeId.")";

$mysqli->multi_query($sql);

$result = $mysqli->use_result();

echo "<table>\n";

while ($data = $result->fetch_array(MYSQLI_ASSOC))
{
if ($data['display'] == 'y')
   {
   echo "<tr>\n";    
   echo "<td>(".$data['menuitem'].")</td>\n<td>".
      $data['haschildren']."</td>\n<td>";
 
   for ($i = 0; $i < $data['level']; $i++)
      echo "   ";
 
   if ($data['menuitem'] == $activeId)
      echo "<b>";
   
   echo "<a href='sp_menutest.php?activeid=".$data['menuitem']."'>".
      $data['description']."</a>";
 
   if ($data['menuitem'] == $activeId)
      echo "</b>";
 
   echo "</td>\n";
   echo "</tr>\n";
   }
}
echo "</table>\n";

$result = $mysqli->next_result();
$result = null;

$mysqli->close();
?>

Enjoy! Further examples using the Nested Set Model will follow.