How to bind array parameters in Yii Framework?

Question

I have the code below:

$inputs = "1,2,3,4,5"; $sql = "SELECT * FROM obj WHERE id IN(:input)"; $commond = Yii::app()->db->createCommand($sql); $commond->bindValue(":input", $inputs , PDO::PARAM_STR); 

But the result of the request is incorrect. How to bind parameters for such an IN condition?

Answer 1

now use it like this:

 $command = Yii::app()->db->createCommand() ->select() ->from('tableName') ->where(array('in', 'id', explode(',', $inputs))); 

I will try to return using the $command->bindValue() method.

Answer 2

Having encountered this problem several times in my projects, I came up with the following Yii workflow using CDbCriteria, which is a bit hacked but gives security for matching parameters.

When applied to your example, my code will look like this:

 $inputs = array(1,2,3,4,5); $criteria = new CDbCriteria(); $criteria->addInCondition('id',$inputs); $sql = 'SELECT * FROM obj WHERE '.$criteria->condition; $command = Yii::app()->db->createCommand($sql); $results = $command->queryAll(true, $criteria->params); 

UPDATE

In fact, there is a much cleaner way to do this built into Yii:

 $results = Yii::app()->db->createCommand() ->select() ->from('obj') ->where(['in', 'id', $inputs]) ->queryAll(); 

See Documents

Answer 3

Using the Yii method chain in CDbCommand to build your request (as in the Uday Sawant answer) is usually a good choice. If creating a query in parts is not ideal, a good alternative is to smooth your array of parameters, so you will not bypass the protection of SQL injections, for example:

 $sql = "SELECT * FROM obj WHERE id IN (:id_array) AND other_field = :other_value"; $args = array( 'id_array' => array(1, 2, 3, 4, 5), 'other_value' => 12, ); // Flatten array arguments into multiple parameters, // replacing with parameter lists in the SQL $newArgs = array(); $replace = array(); foreach($args as $oldKey => $input) { if(!is_array($input)) { $newArgs[$oldKey] = $args[$oldKey]; continue; } $replace[':'.$oldKey] = array(); foreach($input as $i => $value) { $replace[':'.$oldKey][] = ':'.$oldKey.$i; $newArgs[$oldKey.$i] = $value; } $replace[':'.$oldKey] = implode(', ', $replace[':'.$oldKey]); } $sql = strtr($sql, $replace); $query = Yii::app()->db->createCommand($sql); $query->params = $newArgs; $query->queryAll(); 

In this example, the last sql and arguments are:

 SELECT * FROM obj WHERE id IN (:id_array0, :id_array1, :id_array2, :id_array3, :id_array4) AND other_field = :other_value array( 'id_array0' => 1, 'id_array1' => 2, 'id_array2' => 3, 'id_array3' => 4, 'id_array4' => 5, 'other_value' => 12, ) 

In projects where the use of raw SQL is the preferred standard, the biggest advantage is that you can link this as a utility function and reuse it for any query. It's a shame Yii does not automatically expand the arguments of the array this way, but you can also add this support to projects that directly use PDO.

Answer 4

There are two methods in Yii:

1. bindValue() used in the mentioned question
2. bindValues($paramsArray) require, for example, $paramsArray = array(':index'=>$value)

I am using the following code that works fine for me:

 $query = "UPDATE viewing_request SET ViewingApiResponse=:ViewingApiResponse ,ViewingApiData = :ViewingApiData WHERE id='{$id}'"; $executArray = array( ':ViewingApiResponse'=>$data['ViewingApiResponse'], ':ViewingApiData'=>$data['ViewingApiData'] ); $result = Yii::$app->db->createCommand($query) ->bindValues($executArray) ->execute();