Question 7924 in PHP Versions Question posted in
You can find a list of all versions here.

MySQL IN array only processes its first value – PHP Versions

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?

Tags:

2

Answers


  1. 0

    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]);
}
    Login or Signup to reply.
  2. 0

    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);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search