Correct way to bind parameters using MySQL "IN" syntax in Yii2?
I've 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);
You can just use Yii's QueryBuilder functions and everything will be handled automatically. Try this:
$params = [];
$sql = \Yii::$app->db->getQueryBuilder()->update('some_table', ['something' => 'foo'], ['some_id' => [1, 2, 3]], $params);
The 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) }
Yii2's DB functions are based on PDO
.
According to the manual of bindValue
there's no support of value from Array
type. (Third parameter - data_type
).
The solution is to create a string prior to the query, which fits to your IN
clause and bind 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 being replaced by the imploded array as a one string value '1,2,3'
instead of '1','2','3'
(since it's a single placeholder).
In order to solve this issue, i'll suggest using the multiple ?
placeholders.
So instead of having IN (:parents)
you would have IN (?, ?, ?, ? ,....)
, and since we already have an arranged array - we can use count($array)
to know how much placeholders we 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);
}
Please notice the passed value of the first parameter of bindValue;
The reason it's $i+1
and not $i
mentioned in the manual:
For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
For further information and alternative solutions, look at the following answer: https://stackoverflow.com/a/920523/998096