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

Question

Ok, I'm using Yii2 and I'm 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 the value may contain multiple values, such as when using the MySQL syntax IN?

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 the above would only work well if the $parents var only had one value; but if it had multiple values such as 1,2,3 then you would end up with something like '1,2,3' when you really want '1','2','3' OR 1,2,3.

What is the correct way to do this?

Answer 1

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);

Answer 2

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) } 

Answer 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

Answer 4

If data is TRUSTED, this one solution works pretty good:

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

Answer 5

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 6

If someone needs a solution for Yii1:

$db = \Yii::app()->db;

$inCondition = $db
    ->getCommandBuilder()
    ->createInCondition('some_table', 'some_id', $parents);

$command = $db
    ->createCommand("UPDATE some_table SET something = :foo WHERE $inCondition");

$command->bindValue(':foo', 'foo');
$command->execute();

Answer 7

Just do

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

Should probably do the trick.