Sql To Chtml:listdata

Yii 1.1 General Discussion
1

I am creating an CHtml::dropDownList. This list is depending to a value of the department_id.

The Code that is not working:


 $sql = 'SELECT workflow.id, workflow.name FROM workflow INNER JOIN department_workflow

                    ON workflow.id=department_workflow.workflow_id

                    WHERE department_workflow.department_id=' . $department_id;


 $result = Yii::app()->db->createCommand($sql)->queryAll();

$listdata = CHtml::listData($result, 'id', 'name');

foreach($listdata as $value=>$workflow)

 echo CHtml::tag('option', array('value'=>$value),CHtml::encode($workflow),true);

The Code that is supposed to work:

I think the $listdata, should be something like this :


$listdata= SomeModel::model()->findAllByAttributes(array('something' => $someVar));

So you having this sql, how would i solve this proble?

Thank you

2

You’ll need to build the array manually, but you could create your own helper class and method to do it.




$options = array();


foreach (Yii::app()->db->createCommand($sql)->queryAll() as $row)

    $options[$row['id']] = $row['name'];

I don’t understand why you’re outputting option tags. What are you trying to achieve with this?

3

if I am not mistaken he is trying to create a dependent dropdown, if you are take look at this article to get a good understanding

http://www.yiiframework.com/wiki/24/

4

Yes! exactly I am trying to create a dependent dropdown, But my problem is :

I cant use this:


$data=Location::model()->findAll(......some code here...);

Because my data should is coming from 2 tables query.

and when I do like :


$result = Yii::app()->db->createCommand($sql)->queryAll();

$listdata = CHtml::listData($result, 'id', 'name');

foreach($listdata as $value=>$workflow)

 echo CHtml::tag('option', array('value'=>$value),CHtml::encode($workflow),true);

I have this error:

Invalid argument supplied for foreach() (C:\…yii\framework\web\helpers\CHtml.php:2325).

I think the question is how to translate this:




 $sql = 'SELECT workflow.id, workflow.name FROM workflow INNER JOIN department_workflow

                    ON workflow.id=department_workflow.workflow_id

                    WHERE department_workflow.department_id=' . $department_id;

$result = Yii::app()->db->createCommand($sql)->queryAll();

$listdata = CHtml::listData($result, 'id', 'name');

To this:


$$result=Location::model()->findAll(......some code here...);

$listdata = CHtml::listData($result, 'id', 'name');
5

this should do it, I have not tested it though just double check it


$c = new CDbCriteria;

$c->select = ['t.id, t.name'];

$c->join = "INNER JOIN department_workflow dw ON t.id = dw.workflow_id";

$c->compare("dw.department_id", $department_id);


$result=Location::model()->findAll($c);

$listdata = CHtml::listData($result, 'id', 'name');
6

Thank you alirz23 :)

its done!

7

As far as I know, CHtml::listData expects an array of models. That’s why you’re having problems.