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

13,783

Solution 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.

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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();
Share:
13,783

Related videos on Youtube

Brett
Author by

Brett

Updated on September 15, 2022

Comments

  • Brett
    Brett over 1 year

    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?

  • Brett
    Brett almost 9 years
    Wouldn't that just end up with the value being tried as '1,2,3'?
  • Abdul Rehman
    Abdul Rehman almost 9 years
    @Brett it will be 1,2,3 not '1,2,3' and the query will work
  • Brett
    Brett almost 9 years
    @Bsienn Doesn't binding with bindValue cause the value to be quoted though?
  • Brett
    Brett almost 9 years
    @OfirBaruch Well, I guess this would solve the issue with trusted data, but it wouldn't really be advisable to use this method if using user provided data within the IN; I guess I was more looking for a more robust solution and not just one for this particular situation.
  • Brett
    Brett almost 9 years
    Well I just tested and I was right, using this method ended up with '1,2,3' and didn't work correctly.
  • Ofir Baruch
    Ofir Baruch almost 9 years
    I have another idea - using the ? placeholders. I'll edit my answer.
  • Brett
    Brett almost 9 years
    For that to work it would rely on the values being like '1','2','3'.
  • Rjazhenka
    Rjazhenka almost 8 years
    What if I need to implement criteria like some_id NOT IN (1, 2, 3) AND some_field = 2
  • Anton Rybalko
    Anton Rybalko about 7 years
    @Rjazhenka something like ['and', ['not', ['some_id' => [1, 2, 3]]], ['some_field' => 2]]. See specification yiiframework.com/doc-2.0/yii-db-query.html#where()-detail
  • Nebulosar
    Nebulosar over 5 years
    I agree to just use the raw data if you just recovered it from a trusted location.
  • rob006
    rob006 about 5 years
    It will not work if data contains ' or `` at the end of string. I would not use this for anything except integers.