[Solved] How to create dependent DropDown Lists

After playing with Yii for few days, it is time to do some series business. The first requirement is to prepare dependent dorpdown lists. I have three table: tbl_productcategory, tbl_productsubcategory and tbl_productsubsubcategory:

tbl_productcategory : Two fields: id (PK) and category

tbl_productsubcategory: id, cat_id and subcategory.

tbl_productsubsubcategory: id, cat_id, subcat_id and description

While creating records for tbl_productsubsubcategory, I want to display available categories from tbl_productcategory in a DropDownlist (I am able to do this) and then on selecting a category, I want to display the corresponding "subcategory" from tbl_productsubcategory where tbl_productsubcategory.cat_id = selected id in the Category dropdown through Ajax (or something similar - jQuery?)

This is a common and simple requirement in most websites. I looked at post in Yii forum as well as cookbook. I found a cookbook item on the same subject and tried to implement it. Nothing happens when I select an item in the first dropdown list (cat_id). As per the cookbook example, it should call actionDynamicSubcategory() and that will fill the second dropdown list. Am I missing something? Or may be, I should use activeDropDownList (how?)

Here are my files.

View file.


<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(

	'id'=>'productsubsubcategory-form',

	'enableAjaxValidation'=>true,

)); ?>


	<p class="note">Fields with <span class="required">*</span> are required.</p>


	<?php echo $form->errorSummary($model); ?>


	<div class="row">

		<?php echo $form->labelEx($model,'cat_id'); ?>

		<?php

                  $productCategoriesArray = CHtml::listData(Productcategory::model()->findAll(),'id','category');

                   echo $form->DropDownList($model,'cat_id',$productCategoriesArray,

                            array(

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>CController::createUrl('Productsubsubcategory/dynamicSubcategory'),

                                'update'=>'#subcat_id')));


ay;

            ?>

              <?php echo $form->error($model,'cat_id'); ?>

	</div>


	<div class="row">

		<?php echo $form->labelEx($model,'subcat_id'); ?>

		<?php


                       echo $form->dropDownList($model,'subcat_id',array());

            ?>


              <?php echo $form->error($model,'subcat_id'); ?>

	</div>


	<div class="row">

		<?php echo $form->labelEx($model,'description'); ?>

		<?php echo $form->textField($model,'description',array('size'=>60,'maxlength'=>100)); ?>

		<?php echo $form->error($model,'description'); ?>

	</div>


	<div class="row buttons">

		<?php echo CHtml::submitButton($model->isNewRecord ? 'Create' : 'Save'); ?>

	</div>


<?php $this->endWidget(); ?>


</div><!-- form -->

And this is my Controller file




<?php


class ProductsubsubcategoryController extends Controller

{

	/**

	 * @var string the default layout for the views. Defaults to '//layouts/column2', meaning

	 * using two-column layout. See 'protected/views/layouts/column2.php'.

	 */

	public $layout='//layouts/column2';


	/**

	 * @var CActiveRecord the currently loaded data model instance.

	 */

	private $_model;


	/**

	 * @return array action filters

	 */

	public function filters()

	{

		return array(

			'accessControl', // perform access control for CRUD operations

		);

	}


	public function accessRules()

	{

                // Is this Admin user?

		if  (!Yii::app()->user->isGuest  && Yii::app()->user->isAdminUser()  ) {

			$adminUserArray = array(Yii::app()->user->getName());

		} else {

			$adminUserArray = array('');

		}


                // Is this Super User?

		if  (!Yii::app()->user->isGuest  && Yii::app()->user->isSuperUser()  ) {

			$superUserArray = array(Yii::app()->user->getName());

		} else {

			$superUserArray = array('');

		}




		return array(

			array('allow',  // allow admin users to perform 'index' and 'view' actions

				'actions'=>array('index','view'),

				'users'=>$adminUserArray,

			),

			array('allow', // allow super users to perform 'create' and 'update' actions

				'actions'=>array('create','update','dynamicSubcategory'),

				'users'=>$superUserArray,

			),

			array('allow', // allow super user to perform 'admin' and 'delete' actions

				'actions'=>array('admin','delete'),

				'users'=>$superUserArray,

			),

			array('deny',  // deny all users

				'users'=>array('*'),

			),

		);

	}


        public function actionDynamicSubcategory() {

             $data=Productsubcategory::model()->findAll('parent_id=:parent_id',

             array(':parent_id'=>(int) $_POST['cat_id']));


             $data=CHtml::listData($data,'id','subcategory');

              foreach($data as $value=>$subcategory)

                {

                    echo CHtml::tag('option',

                               array('value'=>$value),CHtml::encode($subcategory),true);

                }


        }


	/**

	 * Displays a particular model.

	 */

	public function actionView()

	{

		$this->render('view',array(

			'model'=>$this->loadModel(),

		));

	}


	/**

	 * Creates a new model.

	 * If creation is successful, the browser will be redirected to the 'view' page.

	 */

	public function actionCreate()

	{

		$model=new Productsubsubcategory;


		// Uncomment the following line if AJAX validation is needed

		$this->performAjaxValidation($model);




		if(isset($_POST['Productsubsubcategory']))

		{

			$model->attributes=$_POST['Productsubsubcategory'];




			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}




		$this->render('create',array(

			'model'=>$model,

		));

	}


	/**

	 * Updates a particular model.

	 * If update is successful, the browser will be redirected to the 'view' page.

	 */

	public function actionUpdate()

	{

		$model=$this->loadModel();


		// Uncomment the following line if AJAX validation is needed

		// $this->performAjaxValidation($model);


		if(isset($_POST['Productsubsubcategory']))

		{

			$model->attributes=$_POST['Productsubsubcategory'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}


		$this->render('update',array(

			'model'=>$model,

		));

	}


	/**

	 * Lists all models.

	 */

	public function actionIndex()

	{

		$dataProvider=new CActiveDataProvider('Productsubsubcategory');

		$this->render('index',array(

			'dataProvider'=>$dataProvider,

		));

	}


	/**

	 * Manages all models.

	 */

	public function actionAdmin()

	{

		$model=new Productsubsubcategory('search');

		$model->unsetAttributes();  // clear any default values

		if(isset($_GET['Productsubsubcategory']))

			$model->attributes=$_GET['Productsubsubcategory'];


		$this->render('admin',array(

			'model'=>$model,

		));

	}


	/**

	 * Returns the data model based on the primary key given in the GET variable.

	 * If the data model is not found, an HTTP exception will be raised.

	 */

	public function loadModel()

	{

		if($this->_model===null)

		{

			if(isset($_GET['id']))

				$this->_model=Productsubsubcategory::model()->findbyPk($_GET['id']);

			if($this->_model===null)

				throw new CHttpException(404,'The requested page does not exist.');

		}

		return $this->_model;

	}


	/**

	 * Performs the AJAX validation.

	 * @param CModel the model to be validated

	 */

	protected function performAjaxValidation($model)

	{

		if(isset($_POST['ajax']) && $_POST['ajax']==='productsubsubcategory-form')

		{

                      //$model->selected_catID = $model->cat_id;

			echo CActiveForm::validate($model);

			Yii::app()->end();

		}

	}

}



Anyone… any suggestion?

As you are using CActiveForm the ID created by


echo $form->dropDownList($model,'subcat_id',array());

is not #subcat_id

You can use activeID to get the generated ID like:




'update'=>'#'.CHtml::activeId($model,'subcat_id')



Thanks Madomba for your response. My code has started working little bit. This is my partial view file:


	<div class="row">

		<?php echo $form->labelEx($model,'cat_id'); ?>

		<?php

                  $productCategoriesArray = CHtml::listData(Productcategory::model()->findAll(),'id','category');

                   echo $form->DropDownList($model,'cat_id',$productCategoriesArray,

                            array(

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>CController::createUrl('Productsubsubcategory/dynamicSubcategory'),

                                'update'=>'#'.CHtml::activeId($model,'subcat_id')

                                 )));


            ?>

              <?php echo $form->error($model,'cat_id'); ?>

	</div>



And if I use the following code in the Controller for the function actionDynamicSubcategory(), the subcategory dropdown gets refreshed when I choose a category. (Notice that I have hardcoded $cat_id)


      public function actionDynamicSubcategory() {

            $cat_id = 4;

            $data=Productsubcategory::model()->findAll('cat_id = '. $cat_id) ;


            $data=CHtml::listData($data,'id','subcategory');

            foreach($data as $value=>$subcategory)  {

                echo CHtml::tag('option',

                   array('value'=>$value),CHtml::encode($subcategory),true);

            }

        }




However, if try to get the value of cat_id from the posted data, I get "500 CDbException error.


        public function actionDynamicSubcategory() {

            // The following command does not work

            $data=Productsubcategory::model()->findAll('parent_id=:parent_id',

                    array(':parent_id'=>(int) $_POST['cat_id']));




            $data=CHtml::listData($data,'id','subcategory');

            foreach($data as $value=>$subcategory)  {

                echo CHtml::tag('option',

                   array('value'=>$value),CHtml::encode($subcategory),true);

            }

        }



The code is not able to get the parent_id or the syntax is incorrect. This is what I see in Firebug console.


!DOCTYPE html PUBLIC

	"-//W3C//DTD XHTML 1.0 Transitional//EN"

	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

<title>

CDbException</title>


<style type="text/css">

/*<![CDATA[*/

body {font-family:"Verdana";font-weight:normal;color:black;background-color:white;}

h1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }

h2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }

h3 {font-family:"Verdana";font-weight:bold;font-size:11pt}

p {font-family:"Verdana";font-size:9pt;}

pre {font-family:"Lucida Console";font-size:10pt;}

.version {color: gray;font-size:8pt;border-top:1px solid #aaaaaa;}

.message {color: maroon;}

.source {font-family:"Lucida Console";font-weight:normal;background-color:#ffffee;}

.error {background-color: #ffeeee;}

/*]]>*/

</style>

</head>


<body>

<h1>CDbException</h1>


<h3>Description</h3>

<p class="message">

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'parent_id' in 'where clause'</p>


<h3>Source File</h3>

<p>

C:\xampp\xampp\htdocs\Yii\framework\db\CDbCommand.php(387)</p>


<div class="source">

<pre>

00375: 

00376:             if($this-&gt;_connection-&gt;enableProfiling)

00377:                 Yii::endProfile('system.db.CDbCommand.query('.$this-&gt;getText().')','system.db.CDbCommand.query');

00378: 

00379:             return $result;

00380:         }

00381:         catch(Exception $e)

00382:         {

00383:             if($this-&gt;_connection-&gt;enableProfiling)

00384:                 Yii::endProfile('system.db.CDbCommand.query('.$this-&gt;getText().')','system.db.CDbCommand.query');

00385:             Yii::log('Error in querying SQL: '.$this-&gt;getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00386:             $errorInfo = $e instanceof PDOException ? $e-&gt;errorInfo : null;

<div class="error">00387:             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

</div>00388:                 array('{error}'=&gt;$e-&gt;getMessage())),(int)$e-&gt;getCode(),$errorInfo);

00389:         }

00390:     }

00391: }

</pre>

</div><!-- end of source -->


<h3>Stack Trace</h3>

<div class="callstack">

<pre>

#0 C:\xampp\xampp\htdocs\Yii\framework\db\CDbCommand.php(272): CDbCommand-&gt;queryInternal('fetchAll', 2, Array)

#1 C:\xampp\xampp\htdocs\Yii\framework\db\ar\CActiveRecord.php(1231): CDbCommand-&gt;queryAll()

#2 C:\xampp\xampp\htdocs\Yii\framework\db\ar\CActiveRecord.php(1317): CActiveRecord-&gt;query(Object(CDbCriteria), true)

#3 C:\xampp\xampp\htdocs\Yii\zeusimi\protected\controllers\ProductsubsubcategoryController.php(76): CActiveRecord-&gt;findAll('parent_id=:pare...', Array)

#4 C:\xampp\xampp\htdocs\Yii\framework\web\actions\CInlineAction.php(50): ProductsubsubcategoryController-&gt;actionDynamicSubcategory()

#5 C:\xampp\xampp\htdocs\Yii\framework\web\CController.php(300): CInlineAction-&gt;run()

#6 C:\xampp\xampp\htdocs\Yii\framework\web\filters\CFilterChain.php(133): CController-&gt;runAction(Object(CInlineAction))

#7 C:\xampp\xampp\htdocs\Yii\framework\web\filters\CFilter.php(41): CFilterChain-&gt;run()

#8 C:\xampp\xampp\htdocs\Yii\framework\web\CController.php(1049): CFilter-&gt;filter(Object(CFilterChain))

#9 C:\xampp\xampp\htdocs\Yii\framework\web\filters\CInlineFilter.php(59): CController-&gt;filterAccessControl(Object(CFilterChain))

#10 C:\xampp\xampp\htdocs\Yii\framework\web\filters\CFilterChain.php(130): CInlineFilter-&gt;filter(Object(CFilterChain))

#11 C:\xampp\xampp\htdocs\Yii\framework\web\CController.php(283): CFilterChain-&gt;run()

#12 C:\xampp\xampp\htdocs\Yii\framework\web\CController.php(257): CController-&gt;runActionWithFilters(Object(CInlineAction), Array)

#13 C:\xampp\xampp\htdocs\Yii\framework\web\CWebApplication.php(324): CController-&gt;run('dynamicSubcateg...')

#14 C:\xampp\xampp\htdocs\Yii\framework\web\CWebApplication.php(121): CWebApplication-&gt;runController('Productsubsubca...')

#15 C:\xampp\xampp\htdocs\Yii\framework\base\CApplication.php(135): CWebApplication-&gt;processRequest()

#16 C:\xampp\xampp\htdocs\Yii\zeusimi\index.php(18): CApplication-&gt;run()

#17 {main}</pre>

</div><!-- end of callstack -->


<div class="version">

2010-09-13 18:55:56 Apache/2.2.12 (Win32) DAV/2 mod_ssl/2.2.12 OpenSSL/0.9.8k mod_autoindex_color PHP/5.3.0 mod_perl/2.0.4 Perl/v5.10.0 <a href="http://www.yiiframework.com/">Yii Framework</a>/1.1.4</div>

</body>

</html>



Would you be kind enough to help in fixing the issue? Thank you so much.

As it says… “parent_id” - unknown column… so you don’t have a column parent_id in your table…

In the first example you are using cat_id=$cat_id… in the second example you are using parent_id=$cat_id

Thanks Mdomba for your help. The following Controller Code is working fine.


        public function actionDynamicSubcategory() {

            $cat_id = $_POST['Productsubsubcategory']['cat_id'];

            $data=Productsubcategory::model()->findAll('cat_id=:cat_id',

                    array(':cat_id'=> $cat_id));


            $data=CHtml::listData($data,'id','subcategory');

            foreach($data as $value=>$subcategory)  {

                echo CHtml::tag('option',

                   array('value'=>$value),CHtml::encode($subcategory),true);

            }

        }




Now, my second DropDownList (subcat_id) is responding when I make a selection in the first dropdownlist (cat_id). Now there is just one outstanding issue (hopefully). When the form initially opens during actionCreate() or actionUpdate(), the second dropdownlist is empty. What is the technique to populate the second dropdownlist based on the first item in the first dropdownlist during the Create action or with the $model->cat_id during the Update action? Thank you so much for your help. I am sure that your answer will help other newbies like me.

Just pass the desired values to the second dropdownlist




$desiredValues= ## get the desired values based on parent value ## ;

echo $form->dropDownList($model,'subcat_id',$desiredValues);



Perhaps I did not make my self clear. The view code for generating the second dropdownlist is as follows:


      <div class="row">

                <?php echo $form->labelEx($model,'subcat_id'); ?>

                <?php


                       echo $form->dropDownList($model,'subcat_id',array());

            ?>


              <?php echo $form->error($model,'subcat_id'); ?>

        </div>



So, initially, the second list is empty. The second list gets populated when the users selects an option in the first dropdownlist, which in turns calls controller’s actionDynamicSubcategory() function to fill the list. Therefore, during actionCreate(), when the form is initially displayed, the second list is empty and when the user selects an option in the first dowpdownlist, it fills the second dropdown list based on the selected value. This is working fine. What I want to do is that when the create form is displayed, I want to programatically select the first value in the first dropdown list and accordingly display the second dropdown list filled with the corresponding values.

Similarly, during Update action, the $model already has data for the first list ($model->cat_id). In this case, I want the second list to be populated based on $model->cat_id.

In both cases, I believe, I need to call function, before the form is displayed (may be beforeRender()). I am looking for some tips to accomplish my requirements.

Thanks for your help.

Maybe I was not clear enough…

Even on actionCreate you can set desired value to $model->cat_id

and get all the subcat values based on that value… as you will do with actionUpdate…

Thanks for your suggestion. Now I set $mode->cat_id to a default value in actionCreate. In the view file, I fetch the data for the 2nd dropdown list based on $model->cat_id, instead of generating the empty dropdown list with

echo $form->dropDownList($model,‘subcat_id’,array()); , used earlier.

Thanks again for your help.

I followed the above to create my 2 dropdown lists. First is deptid which will be pre-loaded a list, and the second is pubid which is depending on the value of the deptid selected. However whenever I clicked on the 1st dropdown, the 500 error will be shown in the Chrome console. It seems to me the path defined can’t be found.

My form:




        <table id="table_embded">

            <tr>

                <td><?php echo EHtml::activeLabelEx($model,'deptid'); ?></td>

                <td><?php echo EHtml::activeDropDownList($model, 'deptid', $model->dept_list, 

                        array('prompt'=>'Select department',

                            'onchange'=>  EHtml::ajax(array('type'=>'POST',

                                                            'url'=>CController::createUrl('dynamicpub'),

                                                            'update'=> '#'.EHtml::activeId($model, 'pub')))

                            ));?></td>

            </tr>

        </table>

    

        <table id="table_embded">

            <tr>

                <td><?php echo EHtml::activeLabelEx($model,'pub'); ?></td>

                <td><?php echo EHtml::activeDropDownList($model, 'pub', array());?></td>

            </tr>

        </table>




my controller:




class PubController extends Controller

{

    public function actionDynamicpub()

    {

            //load an empty model of the needed type

            $deptid = $_POST['pub']['deptid'];


            var_dump($deptid);


            $pub_list = dbAdmin::selectPubByDept($model->deptid);


            var_dump($pub_list);


            foreach($pub_list as $pub_name) {

                EHtml::tag('option', array('value'=>$pub_name), EHtml::encode($pub_name), true);

            }

    }


    public function actionEditPubGet()

    {

        $model = new EditPubGetForm();

        $this->render('editPubGet', array('model'=>$model));

    }

}



as you can see there are 2 actions in my controllers, if i change the link ‘url’=>CController::createUrl(‘dynamicpub’) to ‘url’=>CController::createUrl(‘editPubGet’). It will show the editPubGet page without error. So I think the URL defined should be correct.

I saw someone mentioned "partial" view, do I need to create that for this case? Am I missing something here? Appreciate your kind help!

Problem solved.

My working form





        <table id="table_embded">

            <tr>

                <td><?php echo EHtml::activeLabelEx($model,'deptid'); ?></td>

                <td><?php echo EHtml::activeDropDownList($model, 'deptid', $model->dept_list, 

                        array('prompt'=>'Select department',

                            'onchange'=>  EHtml::ajax(array('type'=>'POST',

                                                            'url'=>CController::createUrl('dynamicpub'),

                                                            'replace'=> '#'.EHtml::activeId($model, 'pub')

                                                            )

                                                      )

                            ));?></td>

            </tr>

            <tr>

                <td><?php echo EHtml::activeLabelEx($model,'pub'); ?></td>

                <td><?php echo EHtml::activeDropDownList($model, 'pub', array());?></td>

            </tr>

        </table>



controller:





    public function actionDynamicpub()

    {

        //load an empty model of the needed type

        $deptid = $_POST['EditPubGetForm']['deptid'];


        $pub_list = dbAdmin::selectPubByDept($deptid);


        $editPubGetForm = new EditPubGetForm();

        echo EHtml::activeDropDownList($editPubGetForm,'pub', $pub_list, array('prompt'=>'Select Pub')).'</td>';

    }



found out there were 2 issues:

  1. form post should be

$_POST[‘EditPubGetForm’][‘deptid’];

instead of

$_POST[‘deptid’];

  1. should use replace function instead of update

:D

I bro, you are use a personal function declare: selectPubByDept

$pub_list = dbAdmin::selectPubByDept($deptid);

Please attach the code of this function, thanks !

This is working fine form me. Any Suggestion for modification? please…

view

<?php echo $form->dropDownList($model,‘country_id’,CHtml::listData(Country::model()->findAll(),‘id’,‘name’),

	array(


'prompt'=&gt;'Select Country',


'ajax' =&gt; array(


'type'=&gt;'POST', 


'url'=&gt;CController::createUrl('State/Loadcities'),


'update'=&gt;'#'.CHtml::activeId(&#036;model,'state_id'),

‘data’=>array(‘country_id’=>‘js:this.value’),

))

	); ?&gt;

Controller

public function actionLoadcities()


     {





       &#036;country_id=(int) &#036;_POST['country_id'];


	&#036;data=State::model()-&gt;findAll('country_id=:country_id',array(':country_id'=&gt;&#036;country_id));


	


	&#036;data=CHtml::listData(&#036;data,'id','name');


	foreach(&#036;data as &#036;value=&gt;&#036;name){


    echo CHtml::tag('option', array('value'=&gt;&#036;value),CHtml::encode(&#036;name),true);


	}

}