yii queryAll failes pass parameter

Question

I am passing:

Yii::app()->db->createCommand($query)->queryAll(true,array(':bizid'=>$params));

The query:

 SELECT DISTINCT(BI.item_name)
 FROM items BI 
 WHERE BI.b_id IN (:bizid) ORDER BY BI.item_name ASC

The query failes to find results, but this query gets the results:

SELECT DISTINCT(BI.item_name) as i_name FROM blacklist_items BI WHERE BI.business_id IN   (165,664,842,866,1234,3007,3030,3031,3032) ORDER BY BI.item_name ASC

Why does queryAll fail?

You need a separate placeholder for each possible value: http://stackoverflow.com/questions/9529406/how-to-bind-array-parameters-in-yii-framework?rq=1 — DCoder, Mar 16 '14 at 16:34
It is because of the failure to create a proposer index for the results. It does not like something like $results['DISTINCT(BI.item_name)']. However, $results['i_name'] is good. — crafter, Mar 17 '14 at 07:46

score 0 · Answer 1 · answered Mar 17 '14 at 07:49

0

Assign the ids to an array:

$ids = [165,664,842,866,1234,3007,3030,3031,3032];

The query should be:

$query = "SELECT DISTINCT(BI.item_name)"
 . " FROM items BI"
 . " WHERE BI.b_id IN (" . implode(',', $ids) . ") ORDER BY BI.item_name ASC

answered Mar 17 '14 at 07:49

jhnferraris

1,361
1
12
34

score 0 · Answer 2 · answered Mar 28 '14 at 11:25

Having come across this problem a few times in my projects I have come-up with the following Yii work-around using CDbCriteria which is a little hacky, but gives the security of param count matching.

When applied to your example my code would be:

$business_ids = array(1,2,3,4,5);
$criteria = new CDbCriteria();
$criteria->addInCondition('business_id',$business_ids);

$sql = 'SELECT DISTINCT(item_name) 
          FROM items 
         WHERE '.$criteria->condition.'
      ORDER BY item_name ASC';

$command = Yii::app()->db->createCommand($sql);
$results = $command->queryColumn($criteria->params);

yii queryAll failes pass parameter

2 Answers2