Correct way to bind parameters using MySQL "IN" syntax in Yii2?

Question

Ok, I use Yii2 , and I am familiar with preparing / binding data when using mysql queries such as:

 $sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id=:some_id"); $sql->bindValue(':some_id', $some_id); 

But what about when a value can contain multiple values, for example, when using the MySQL IN syntax?

For example:

 $sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (:parents)"); $sql->bindValue(':parents', $parents); 

Now, as I understand it, this would work well only if $parents var had only one value; but if it had several values, like 1,2,3 , then you would have something like '1,2,3' when you really want '1','2','3' OR 1,2,3 .

What is the right way to do this?

Answer 1

I ended up doing it like this:

 $parents_safe = ''; $parents_sep = explode(',', $parents); foreach ($parents_sep as $parent) { $parents_safe .= $this->db->quoteValue($parent) . ','; } $parents_safe = rtrim($parents_safe, ','); 

Where $this->db is an instance of Yii::$app->db .

Answer 2

You can simply use the Yii QueryBuilder functions and everything will be processed automatically. Try the following:

 $params = []; $sql = \Yii::$app->db->getQueryBuilder()->update('some_table', ['something' => 'foo'], ['some_id' => [1, 2, 3]], $params); 

Result:

 string(78) "UPDATE `some_table` SET `something`=:qp0 WHERE `some_id` IN (:qp1, :qp2, :qp3)" array(4) { [":qp0"]=> string(3) "foo" [":qp1"]=> int(1) [":qp2"]=> int(2) [":qp3"]=> int(3) } 

Answer 3

Yii2 DB functions are based on PDO . According to the bindValue manual bindValue no support for values ​​from the Array type. (The third parameter is data_type ).

The solution is to create a string before the query that matches your IN clause and binds it as a string.

Something like:

 $parents = "1,2,3"; /* Or in case you already have an array of the desirable ids: $parents_array = array(1,2,3); $parents = implode(",",$parents_array); */ $sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (:parents)"); $sql->bindValue(':parents', $parents); 

Edit

It seems that the placeholder is replaced by the impotent array as a single string value of '1,2,3' instead of '1','2','3' (since it is the only placeholder).

To solve this problem, I suggest using multiple placeholders ? . Therefore, instead of IN (:parents) , you will have IN (?, ?, ?, ? ,....) , and since we already have an organized array, we can use count($array) to find out how many placeholders we have need to put.

 //$parents = array(1,2,3); $placeholders = str_repeat('?,', count($parents) - 1). '?'; $sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (".$placeholders.")"); foreach($parents as $i => $parent){ $sql->bindValue($i+1, $parent); } 

Pay attention to the passed value of the first parameter bindValue; The reason it is $i+1 and not $i is listed in the manual:

For a prepared statement using question mark placeholders, this will be the 1-indexed parameter position .

For more information and alternative solutions, consider the following answer: stack overflow

Answer 4

I found this solution

 $params = []; $sql = \Yii::$app->db->getQueryBuilder()->buildCondition(['IN', 'some_id', $ids], $params); //$sql = some_id NOT IN (:qp0, :qp1, :qp2) //$params = [':qp0'=>1, ':qp1'=>2, ':qp2'=>3] $this->db->createCommand("UPDATE some_table SET something='foo' WHERE $sql", $params); 

Answer 5

If the data is TRUSTED , this one solution works very well:

 $db = Yii::$app->db; $ids = "'" . implode("','", $ids_array) . "'"; $result = $db->createCommand(" UPDATE some_table SET something='foo' WHERE some_id IN ($ids) ")->queryColumn(); 

Answer 6

Just do

 $parents = implode("','", $parrent_array); $sql->bindValue(':parents', $parents); 

Perhaps this is a trick.