CODEWITHSUNDEEP
phpmysqlyiiyii2
Brett
Brett

Reputation: 20049

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

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?

Upvotes: 20

Views: 18044

Answers (7)

Modder
Modder

Reputation: 893

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

Upvotes: 0

Alliswell
Alliswell

Reputation: 1583

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

Upvotes: 1

Rjazhenka
Rjazhenka

Reputation: 1458

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

Upvotes: 9

laszlovl
laszlovl

Reputation: 491

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

Upvotes: 8

Brett
Brett

Reputation: 20049

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.

Upvotes: 0

shyyawn
shyyawn

Reputation: 1

Just do

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

Should probably do the trick.

Upvotes: -5

Ofir Baruch
Ofir Baruch

Reputation: 10356

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

Upvotes: 4

Related Questions