Yii-CMenu menu controlled by the database

This is a simple database-based menu system that uses CMenu rendering.

This is a simple database-based menu system that uses CMenu rendering.

Database structure

CREATE TABLE IF NOT EXISTS `menu` ( `menu_id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `date_added` datetime NOT NULL, `last_updated` datetime NOT NULL, `status` enum('active','inactive') NOT NULL, PRIMARY KEY (`menu_id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `menu_item` ( `item_id` int(11) NOT NULL auto_increment, `parent_id` int(11) default NULL, `menu_id` int(11) NOT NULL, `label` varchar(255) NOT NULL, `url` text NOT NULL, `description` text NOT NULL, `date_added` datetime NOT NULL, `last_updated` datetime NOT NULL, `sort_order` int(11) NOT NULL, `status` enum('active','inactive') NOT NULL, PRIMARY KEY (`item_id`), KEY `fk_menu_item_menu1` (`menu_id`), KEY `fk_menu_item_menu_item1` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ALTER TABLE `menu_item` ADD CONSTRAINT `fk_menu_item_menu1` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`menu_id`) ON DELETE CASCADE ON UPDATE NO ACTION, ADD CONSTRAINT `fk_menu_item_menu_item1` FOREIGN KEY (`parent_id`) REFERENCES `menu_item` (`item_id`) ON DELETE SET NULL ON UPDATE NO ACTION;

Methods used to retrieve menu items in the model

public function getItems($menu_id, $parent_id=null) { $results = Yii::app()->getDb()->createCommand(); $results->select('item_id, label, url')->from('{{menu_item}}'); if($parent_id === null) $results->where('menu_id=:mid AND parent_id IS NULL', array(':mid'=>(int)$menu_id)); else $results->where('menu_id=:mid AND parent_id=:pid', array(':mid'=>(int)$menu_id, ':pid'=>$parent_id)); $results->order('sort_order ASC, label ASC'); $results = $results->queryAll(); $items = array(); if(empty($results)) return $items; foreach($results AS $result) { $childItems=$this->getItems($menu_id, $result['item_id']); $items[] = array( 'label' => $result['label'], 'url' => $result['url'], 'itemOptions' => array('class'=>'listItem'), 'linkOptions' => array('class'=>'listItemLink', 'title'=>$result['label']), 'submenuOptions'=> array(), 'items' => $childItems, ); } return $items; }

CMenu initialization

//get the menu with id #2 $items=$this->getItems(2); $menu = array( 'id' => 'nav', 'activeCssClass'=>'selected', 'linkLabelWrapper'=>null, 'htmlOptions'=>array('class'=>'topNav'), 'items'=>$items ); $this->widget('zii.widgets.CMenu', $menu);