解决方案使用PDO将数组放入IN子句中

Question

I've been trying the two most upvoted solutions in this thread about workaround methods to make an array work in PDO prepared statements.

Solution 1

$qMarks = str_repeat('?,', count($ids) - 1) . '?';
$sth = $db->prepare("SELECT * FROM myTable WHERE id IN ($qMarks)");
$sth->execute($ids);

Solution 2

$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));

$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(' . $inQuery . ')'
);

But I got an error of

Invalid parameter number: mixed named and positional parameters

when applying each to my code. Does the error mean that if I use one of those solutions for $_POST["group"] (array), I can't use BindValue for $_POST["reply_name"]? Is there any solution?

Original Code (it only works if the IN Clause only has one value):

  $id_group = $_POST["group"];
  $reply_name = $_POST["reply_name"];

  foreach($id_group as $key => $id)
  {
    $ins[] = "".($id)."";
  }

  $in = implode(",",$ins);

  $sql = "SELECT a.name from `reviews` a 
           INNER JOIN `users` b 
            ON a.id = b.id  
           WHERE a.reply_name = :reply_name 
           AND a.id_group IN (:id_group) 
           ORDER BY a.id_group";

  $users = $dbh->prepare($sql);
  $users->bindValue(':reply_name',$reply_name);
  $users->bindValue(':id_group',$in);
  $users->execute();

  foreach($users as $row)
  {
    echo $row["name"];
  }

Answer 1

You cannot use :namedParameters and ? placeholders at the same time, as the error says. It's either or, never mixed. You'll either have to convert all your placeholders to ? and bind all your parameters using positional offsets:

$users->bindValue(1, $reply_name);
...
$i = 3;
foreach ($ids as $id) {
    $users->bindValue($i++, $id);
}

// or

$users->execute(array_merge(array($reply_name, $in), $ids));

or you make named placeholders for each id:

$placeholders = array_map(function ($num) { return ":id$num"; }, range(1, count($ids)));
$stmt = $pdo->prepare(sprintf('SELECT ... IN (%s)', join(', ', $placeholders)));
// .. bind :id1, :id2 etc. similar to above example