找到你要的答案

Q:Correct way to bind parameters using MySQL “IN” syntax in Yii2?

Q:正确的方法来绑定参数使用MySQL数据库”中的“语法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?

好的,我用yii2我熟悉备/绑定时使用MySQL查询的数据,如:

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

但对于当的值可能包含多个值,例如当使用MySQL语法吗?

例如:

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

现在我明白了上面的就只有父母只有一美元的价值VaR的工作;但如果有多个值,如1,2,3,那么你最终会像“1,2,3”当你真的想要‘1’,‘2’,3’或1,2,3。

做这件事的正确方法是什么?

answer1: 回答1:

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: http://stackoverflow.com/a/920523/998096

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

解决方案是在查询之前创建一个字符串,它适合于您的子句,并将其绑定为字符串。

像:

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

编辑

看来,占位符取代崩溃阵列一一字符串值”1,2,3”而不是“1”,2’,3’(因为它是一个占位符)。

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: http://stackoverflow.com/a/920523/998096

answer2: 回答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);

我找到了这个解决方案

$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);
answer3: 回答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) } 

你可以只使用Yii的querybuilder功能,一切都会自动处理。试试这个:

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

结果:

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) } 
answer4: 回答4:

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.

我最后这样做了:

$parents_safe = '';
$parents_sep = explode(',', $parents);

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

$parents_safe = rtrim($parents_safe, ',');

在美元- >;DB是一个实例::$应用>;数据库。

answer5: 回答5:

Just do

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

Should probably do the trick.

刚做的

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

应该做的伎俩。

php  mysql  yii  yii2