"Only process the first value of the MySQL IN array"

Question

I am using PHP programming to create a PDO statement.

The PDO preprocessing statement is as follows:

Update `log` SET `id` = ? WHERE `id` IN ( ? );

INThe variable contents in the array are integers.

The first ? is a single integer. The second ? is an array of integers, so I concatenate them into the string 1,2,3, this string usually works for INarray.

So the complete code is as follows:

public function mergeIDs($newID,$changeTheseIDs){ // (integer and array of integers, respectively)
    $inSet = implode(',',$changeTheseIDs);
    $query = $this->connect()->prepare("UPDATE `log` SET `id` = ? WHERE `id` IN ( ? );");
    $query->execute([$newID,$changeTheseIDs]);
}

The problem seems to be that SQL only processes the first integer ID inserted into the IN array. Any ID that matches an integer in the IN array should be changed.

If I run the SQL in the SQL tool on my server like this:

UPDATE `log` SET `id` = 5 WHERE `id` IN (1,2,3);

It works fine, I change all the ID's 1, 2 or 3 to 5.

But my PDO version only changes 1 to 5, 2 and 3 remain the same.

Any ideas?

Answer 1

Try code like this:

public function mergeIDs($newID,$changeTheseIDs){ //（分别为整数和整数数组）
$mergeSet = array_merge([$newID],$changeTheseIDs);
$marks = array_fill(0, count($changeTheseIDs), '?');
$query = $this->connect()->prepare("UPDATE `log` SET `id` = ? WHERE `id` IN (". implode(',',  $marks) .");");
$query->execute($mergeSet);
}

Answer 2

You need to prepare one ? for each integer in the IN clause, not just one.

public function mergeIDs($newID,$changeTheseIDs){ //（分别是整数和整数数组）
    $inPH = implode(',', array_fill(0, count($changeTheseIDs), '?'));
    $query = $this->connect()->prepare("UPDATE `log` SET `id` = ? WHERE `id` IN ( {$inPH} );");
    $query->execute([$newID, ...$changeTheseIDs]);
}