ORDER BY FIELD(id, ?) not working in 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('\r\n\t\t\tSELECT * F...')

`WHERE id IN` expects a list like `('1','2')`, but you gave is a string like `'blah'`. Also Field probably wants an int instead of a string. — bassxzero, Feb 01 '17 at 21:00
@jay-blanchard I don't think this is a dupe. His code doesn't appear to try and replace a table or column name like the question you linked. — bassxzero, Feb 01 '17 at 21:14
that is the problem @bassxzero my question is not duplicated — Abdalla Arbab, Feb 01 '17 at 21:24
I'm aware it's not a dup., but i didn't close it and I can't open it — bassxzero, Feb 01 '17 at 21:26
it was because you duplicate it. I did not say that because you reply lately — Abdalla Arbab, Feb 01 '17 at 21:54
*"He is not responding. I really hate that "* is what you said. ¯\\_(ツ)_/¯ — Jay Blanchard, Feb 01 '17 at 21:58
lets make that clear. responding => Disable Duplication ¯\_(ツ)_/¯ hahaha — Abdalla Arbab, Feb 01 '17 at 22:01

miken32 · Accepted Answer · 2017-02-01T22:14:20.403

4

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);

edited Feb 01 '17 at 22:14

answered Feb 01 '17 at 22:09

miken32

42,008
16
111
154

I got the mistake, so I forgot that by using Order By, I introduced another `?` for params, but I was just using only one in execute! This solved it! – Jones G Aug 05 '20 at 17:15

score 0 · Answer 2 · answered Feb 01 '17 at 22:09

0

Try this. I know it's missing your order by, but we can get to that in a minute.

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

$inQuery = $inQuery = implode(',', array_fill(0, count($res), '?'));


$Search = $db->prepare("
    SELECT * FROM articles
    WHERE id IN ({$inQuery})    
");

$Search->execute($res);

answered Feb 01 '17 at 22:09

bassxzero

4,838
22
34

are you certain that i don't need FIELD because in my code documentation i have `The ORDER BY FIELD clause is important otherwise the database engine will not return the results in required order` – Abdalla Arbab Feb 01 '17 at 22:22

ORDER BY FIELD(id, ?) not working in PDO

2 Answers2