Database driven Cmenu

You are viewing revision #1 of this wiki article.
This is the latest version of this article.

  1. Database structure:
  2. Model method to retrieve a certain menu items:
  3. CMenu initialization:

This is a simple example that will give you a base for designing a database driven menu system that will use CMenu to be rendered.

Database structure:

[sql]
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;

Model method to retrieve a certain menu items:

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);