ORDER BY FIELD（id，？）无法在PDO中工作

Question

I am trying to execute a query that shows only articles inside this array $res. It contains article IDs

$res = Array ( [0] => 42 [1] => 41 );

$res1 = $res;
$res2 = $res;

$Search = $db->prepare("
    SELECT * FROM articles
    WHERE id IN :res1
    ORDER BY FIELD(id, :res2);
");

$Search->execute([
    ':res1' => $res1,
    ':res2' => $res2
]);

but it is returnig this error

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? ORDER BY FIELD(id, ?)' at line 2 in C:\xampp\htdocs\index.php:16 Stack trace: #0 C:\xampp\htdocs\index.php(16): PDO->prepare(' \t\t\tSELECT * F...')

Answer 1

You're trying to pass an array as a parameter. That won't work. Also, IN takes a comma separated list inside parentheses, and that's not how to declare an array in PHP.

$res = [42, 41];

$params = array_merge($res, $res);
// build a big list of question marks
// for a two element array we get ?,?
$placeholder = trim(str_repeat("?,", count($res)), ",");

$Search = $db->prepare("
    SELECT * FROM articles
    WHERE id IN ($placeholder)
    ORDER BY FIELD(id, $placeholder);
");

$Search->execute($params);