use parameter in query only return one row

Yii 2.0 General Discussions
1

hi

why when i use parameter in my query, only on row return:




$dataProvider = new ActiveDataProvider([

            'query' => Item::findBySql('SELECT * FROM item WHERE id IN ( :var );', [':var' => $items_id]),

        ]);

but when use this, it return all expected rows:




$dataProvider = new ActiveDataProvider([

    'query' => Item::findBySql('SELECT * FROM item WHERE id IN ('.$items_id.');'),

]);
2

I believe the issue is that it attempts to bind it as a string. So in your first example, the query would come out as:


SELECT * FROM item WHERE id IN ('1,2,3,4,5');

Whereas in the second you’re injecting it directly. If you’re 100% sure items_id is safe, I suppose you could throw it in directly (I’ve done it a few times, only when I know that the param only contains a numeric set), otherwise perhaps you want to make use of the query builder. For instance, you could rewrite the above as:


$query = Item::find()->where(['in', 'id', $itemIds]);

Where $itemIds is an array of numeric item ids. You can read up more on the query builder here: http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html