combogrid
In this tutorial I will show you how to use the yiicombogrid plugin to dynamically select item based on previously selected item. Here I will show you how to select state based on selected country. First we will create two tables ‘country’ and ‘state’. Table country has two coloumns id(pk) and name. Below is the query for creating table ‘country’.

CREATE TABLE IF NOT EXISTS `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Table ‘state’ has three coloumns id(pk),country_id(fk) and name.Below is the query for creating table ‘state’.

CREATE TABLE IF NOT EXISTS `state` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `country_id` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
ALTER TABLE `state`
ADD CONSTRAINT `state_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON UPDATE CASCADE;

Now we will insert values into the ‘country’ table.

INSERT INTO `report`.`country` (`id`, `name`) VALUES (NULL, 'USA'), (NULL, 'UK');

Next we will insert values into the ‘state’ table.

INSERT INTO `report`.`state` (`id`, `country_id`, `name`) VALUES (NULL, '1', 'Alabama'), (NULL, '1', 'Alaska'), (NULL, '1', 'Arizona'), (NULL, '1', 'Arkansas'), (NULL, '1', 'California'), (NULL, '2', 'Aberdeen'), (NULL, '2', 'Armagh'), (NULL, '2', 'Bangor'), (NULL, '2', 'Bath'), (NULL, '2', 'Belfast');

So we have created the tables and inserted values into them. Next we will creaet the view file for selecting the state. Create a file ‘myapp\protected\views\site\state.php’ and paste the below code in it.

<?php
$form=$this->beginWidget('CActiveForm', array(
'id'=>'subject-form',
'action' => Yii::app()->createUrl('//'),
'enableAjaxValidation' => true,
'enableClientValidation' => true,
'clientOptions' => array(
'validateOnSubmit' => true,
'afterValidate'=>'js:function(form,data,hasError){
if(!hasError){
$.ajax({
"type":"POST",
"url":"'.CHtml::normalizeUrl(array("//")).'",
"data":form.serialize(),
"success":function(data){$.fn.yiiGridView.update("subjects-grid");$("#subject-form")[0].reset();$("#Subjects_editmode").val(0);return false;},
"error":function(xhr, status, error){alert(xhr.responseText);},
});
}
}'
),
'htmlOptions' => array(
"class"=>"form-horizontal"
),
));

echo $form->hiddenField($model,'editmode',array('value'=>0));
?>
<?php echo $form->labelEx($model,'country',array('class'=>'control-label')); ?>

<?php
$DisplayColumns = "id,name";

$query ="select id,name from country where name LIKE :searchTerm ORDER BY name";

$cntrlr = Yii::app()->createAbsoluteUrl('combogrid/generateoptions', array('sql'=>$query,'dColumns' => $DisplayColumns));

$this->widget('ext.combogrid.CombogridWidget',array(
'model'=>$model,
'attribute'=>'country',
'htmlOptions'=>array('class'=>'form-control','title'=>'Name'),
'options'=>array(
'minLength'=>'1',
'showOn'=>true,
'url'=> $cntrlr,
'debug'=>true,
'width'=>500,
'alternate'=> true,
'colModel'=>array( array('columnName'=>'id','width'=>'10','label'=>'Id','align'=>'left','hidden'=>true),
array('columnName'=>'name','width'=>'50','label'=>'Name','align'=>'left')
),
'select'=>'js: function( event, ui ) {
$("#country_id").val( ui.item.id );
$("#State_country").val( ui.item.name );
$("#State_state").combogrid("option","extraParam",ui.item.id);
return false;
} '
),
));
echo $form->hiddenField($model,'country_id',array('id'=>'country_id'));
echo $form->error($model,'country');
?>

<div class="clearfix"></div>
<div>&nbsp;</div>
<div class="clearfix"></div>

<?php echo $form->labelEx($model,'state',array('class'=>'control-label')); ?>
&nbsp;&nbsp;&nbsp;
<?php
$DisplayColumns = "id,name";

$query ="select id,name from state where name LIKE :searchTerm and country_id=:extraParam ORDER BY name";

$cntrlr = Yii::app()->createAbsoluteUrl('combogrid/generateoptionsextra', array('sql'=>$query,'dColumns' => $DisplayColumns));

$this->widget('ext.combogrid.CombogridWidget',array(
'model'=>$model,
'attribute'=>'state',
'htmlOptions'=>array('class'=>'form-control','title'=>'Name'),
'options'=>array(
'minLength'=>'1',
'showOn'=>true,
'url'=> $cntrlr,
'debug'=>true,
'width'=>500,
'alternate'=> true,
'colModel'=>array( array('columnName'=>'id','width'=>'10','label'=>'Id','align'=>'left','hidden'=>true),
array('columnName'=>'name','width'=>'50','label'=>'Name','align'=>'left')
),
'select'=>'js: function( event, ui ) {
$("#state_id").val( ui.item.id );
$("#State_state").val( ui.item.name );
return false;
} '
),
));
echo $form->hiddenField($model,'state_id',array('id'=>'country_id'));
echo $form->error($model,'state');
?>


<?php echo CHtml::submitButton('Save',array('class'=>'btn btn-primary top-buffer')); ?>
<?php $this->endWidget(); ?>

Here note the line

'$("#State_state").combogrid("option","extraParam",ui.item.id);' 

where we are assigning an extra parameter to the state combogrid. In the query to select the states we can use the condition ‘country_id=:extraParam’ to filter the states based on country. Now we have to create the model for this combogrid. Create a file named ‘CombogridExtraModel.php’ in the ‘myapp\protected\models\CombogridExtraModel.php’ folder and paste the below code.

<?php

class CombogridExtraModel extends CActiveRecord
{

public static function model($className=__CLASS__)
{
return parent::model($className);
}


public function tableName()
{
return 'state';
}

public function totalcount($query,$searchTerm,$extraParam)
{
$connection=Yii::app()->db;
$command=$connection->createCommand($query);
$command->bindParam(":searchTerm", $searchTerm, PDO::PARAM_STR);
$command->bindParam(":extraParam", $extraParam, PDO::PARAM_STR);
$result = $command->queryAll();
return $result;
}

public function selectall($SQL,$searchTerm,$extraParam)
{
$connection=Yii::app()->db;
$command1=$connection->createCommand($SQL);
$command1->bindParam(":searchTerm", $searchTerm, PDO::PARAM_STR);
$command1->bindParam(":extraParam", $extraParam, PDO::PARAM_STR);
$result = $command1->queryAll();
return $result;
}

}

Next create a model for the form at ‘myapp\protected\models\State.php’ and paste the below code.

<?php
class State extends CActiveRecord
{

public $country;
public $state;
public $state_id;

/**
* Declares the validation rules.
*/
public function rules()
{
return array(
array('id,name', 'required'),
array('', 'safe'),
);
}

public function tableName()
{
return 'state';
}

}

Now create the action in the ‘myapp\protected\controllers\SiteController.php’

public function actionState()
{
$model=new State;
if(isset($_GET['State']))
{
$model->unsetAttributes();
$model->attributes = $_GET['State'];
}
$this->render('state',array('model'=>$model));
}

Next create the menu link for your view by adding the below code into ‘myapp\protected\views\layouts\main.php’.

array('label'=>'State', 'url'=>array('/site/state')),

Now click the menu link to view the page and first select the country and see the filtered states in the next combogrid.