Geek Answers Handbook

Escape Array of Strings for IN Statement PDO MYSQL

Instead of starting a loop to update the values ​​in the table, I would like to use the IN statement (assuming it is faster?).

I have an array of values:

$array (
    1 => Tom
    2 => Bob
    3 => Sally String
    4 => Pesce is Italian for "fish"
   )

I use a loop because I can prepare each line separately to account for potentially bad characters:

$sql = "UPDATE table SET data = 1 WHERE my_string = ?";
$s = pdoObject->prepare($sql);

foreach($array as $string){
    $s->execute(array($string));
}

I would really like to use the IN statement (again, counting this faster, please tell me if I'm wrong). The problem is that creating an IN statement will lead to some errors, given the different types of characters present in my string array. eg;

$inString = '"'.implode('","',$array).'"';
// $inString would be "Tom","Bob","Sally String","Pesche is Italian for "fish"";

Is there any way to prepare this type of request? Or the "execute many" function? Usually I see arrays of strings from 5 to 50 units long.

###### WHY THIS QUESTION IS UNIQUE ######

IN()?, , .

+4
1

, . ? . - :

<?php
  $array = array('Tom', 'Bob', 'Sally\ String', 'Pesce is Italian for "fish"');
  $placeholders = implode(',', array_fill(0, count($array), '?'));

  $sql = "UPDATE table SET data = 1 WHERE my_string IN ( $placeholders )";
  // $sql now contains "UPDATE table SET data = 1 WHERE my_string IN ( ?,?,?,? )"
  $s = $pdo->prepare($sql);
  $s->execute($array);
?>

, , in, . $s->execute($array) $s->execute(array_values($array)), .

+5

More articles:


All Articles