How do I create a parameterized database update st

Question

I tried

$sql = "update ad_group_keyword set status = :status where google_id not in (:google_id)";
Yii::$app->db->createCommand($sql)
  ->bindValue(':status', Constants::DELETED)
  ->bindValue(':google_id', join(',',$googleIds), \PDO::PARAM_INT)
  ->execute();

but it turned the array of ids into one giant string, despite the PDO::PARAM_INT. I also tried

  ->bindValue(':google_id', $googleIds)

but it gave an 'Array to string conversion' in vendor/yiisoft/yii2/db/Command.php:172. I ended up using

$sql = "update ad_group_keyword set status = :status where google_id not in (" . join(',',$googleIds) . ")";

Answer 1

You'll need to bind each of the array values individually. Something like this:

$sql = "UPDATE ad_group_keyword
        SET status = :status
        WHERE google_id NOT IN(%s)";

$bindValues = array();
$i = 0;
foreach ($googleIds as $value)
{
    $bindValues[':googleId'.$i++] = $value;
}

$sql        = sprintf($sql, join(', ', array_keys($bindValues)));
$sqlCommand = Yii::$app->db->createCommand($sql);
$sqlCommand->bindValue(':status', Constants::DELETED);

foreach ($bindValues as $key => $value)
{
    $sqlCommand->bindValue($key, $value, \PDO::PARAM_INT);
}

$sqlCommand->execute();

However, I'm only basing this example on your code and I'd look into Yii's manual to see if there already isn't a method that does all of this work for you ... it shouldn't be that hard to safely execute an SQL query using IN().

Answer 2

You shouldn't have a join in there at that place. That is where it is being turned into a string. You want to iterate through your list of ids and bindValue each one to the variable in turn.

Answer 3

I suggest use QueryBuilder for this function:

$command = Yii::$app->db->createCommand();
$result = $command->update( // create a update sql
     'ad_group_keyword',  // table
     ['status'=>1],  // update set
     ['NOT IN', 'google_id', [1,2,3]] // where
)->execute();

You can read the \yii\db\Command::update() DOC, and how to set condition