Purpose

I thought I would share my experience trying to setup a database driven CMenu system. Hopefully this will help some people put together a database driven CMenu.

Here are the tables I created for the menu.
Menu & Menugroups ~~~ [sql] CREATE TABLE IF NOT EXISTS menugroups ( id int(11) NOT NULL AUTO_INCREMENT, group_name varchar(255) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS menu ( id int(11) NOT NULL AUTO_INCREMENT, label varchar(128) NOT NULL, view_page varchar(128) NOT NULL, url varchar(128) NOT NULL, options varchar(255) NOT NULL, urloptions varchar(255) NOT NULL, description text NOT NULL, status int(11) NOT NULL, position int(11) NOT NULL, menu_group_id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

In the layout file for my site I added the following funtion/code:
(/protected/views/layouts/main.php)

```php
function getMenuItems($id=null)
{
    $results = Yii::app()->getDb()->createCommand();
    $results->select('*')->from('menu');
    $results->where('menu_group_id='.$id);
    $results->order('position ASC');
    $results = $results->queryAll();
                
                $items = array();
                $addid = "";
                $permissions = 1;

foreach($results AS $result)
        {
        
        if(yii::app()->user->id){
          /*This code helps to hide the login menu when logged in. 
          Under options for login I set "IsGuest"*/
          if(Yii::app()->user->isGuest<>1 & $result['options']<>'IsGuest') 
            { 
             $permissions = 1;  
            } 
            else { 
             $permissions = 0; 
                  } 
         if($result['urloptions']==1) {
            //I use this code to apply id to the URL  this can be modified to suit your needs                                                   
            $addid = array($result['url'],'id'=>yii::app()->user->user_id);
            }
            else {
            $addid = array($result['url']);
            }
                                                                
          }
          else {
                $addid = array($result['url']);
                                                                }
            $items[] = array(
               
               'label'     => $result['label'],
               'url'       => $addid,
               'visible'   => $permissions,
                        );
        }
                               
        return $items;
                
}

/*Checking if user has logged in and what group he is a member of.  This will only work if you set the users group of your logged in user in your UserIdentity.php (authenticate function.)   I do this because it is easier to assign menus to those groups. */  

if(yii::app()->user->id) {
$group_id = yii::app()->user->group_id;
}
else {
$group_id = 4;
}
```

Note this code must be added to UserIdentity.php 
under the authenticate function just after $this->errorCode=self::ERROR_NONE;
This is because I am using user groups to help establish menu permissions.

```php
$this->setState('group_id', $user->group_id);
```


Now call the widget

```php
$this->widget('zii.widgets.CMenu',array(
                          'id'=>'mynav',
                          'items'=>getMenuItems($group_id),
             ));

```