MySQL IN array only processes its first value

Question

I am programmatically creating a PDO statement using PHP.

The PDO prepared statement is:

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

Where the variable content in the IN array are integers.

The first ? is a single integer. The second ? are integers in an array, so I am imploding them to the string 1,2,3 that would normally fit in the IN array.

So the full deal is:

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 issue seems to be that the SQL only processes the FIRST of the integer IDs that are inserted in the IN array. Any ID matching one of the integers 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, and I get all IDs that were 1, 2, or 3 changed to 5.

But my PDO version changes only the 1s to 5s. 2s and 3s are left the same.

Any thoughts?

score 1 · Accepted Answer · answered Aug 25 '22 at 01:17

1

You'll need a ? for each integer in the IN clause, you can't just have one.

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

answered Aug 25 '22 at 01:17

Musa

96,336
17
118
137

Thank you both. These are great answers, but I expect the question will be cashiered. I searched like a dog for the question that is referenced above, but SO's algorithm showed me nothing of the sort when I submitted the question. All in a day's work. – Parapluie Aug 25 '22 at 14:06
1

@Parapluie don't use SO search. Use google with `site stackoverflow.com`. If you just add the word PDO to your title the dupe comes up on the first page. – Nick Aug 26 '22 at 00:23

score 1 · Answer 2 · answered Aug 25 '22 at 01:20

Try something like this :

public function mergeIDs($newID,$changeTheseIDs){ // (integer and array of integers, respectively)
$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);
}

MySQL IN array only processes its first value

2 Answers2