trouble getting mysql rows using limit

Question

I have a php script called via an ajax request each time a user presses a button that is supposed to get the next 10 rows of a database. When the button is pressed, nothing happens and I get no errors in the console or from the php

$query = $conn->prepare('SELECT name, file_loc, img_id, filter, votes FROM images WHERE user_id=? ORDER BY votes DESC LIMIT ?, 10');
$query->execute(array($user_id, $skip));
$result = $query->fetchAll();

When I go to phpmyadmin, manually fill in the variables, and run the query directly, it runs properly.

In the php when I add echo $skip . ' ' . $user_id;to the end of the script, it shows that all the variables are what they are supposed to be. Additionally if I edit the end of the query to use a static number instead of plugging the variable to read LIMIT 10, 10, then everything works fine (although not being a variable, it can't increment by 10).

I have no idea why this isn't running properly but I feel like I'm overlooking something obvious. Any ideas?

Answer 1

When in emulation mode (which is on by default), PDO is substituting placeholders with actual data. And with "lazy" binding PDO treats every parameter as a string.
As a result, the query become

LIMIT '10', 10

which is obviously wrong syntax that causes query to fail.

So, you have 2 solutions:
By turning emulation off (as mysql can sort all placeholders properly)
Or by binding the number explicitly, like in the Kalpesh's answer. But don't forget to set proper type (PDO::PARAM_INT) for this variable.

To turn emulation off run this code after connect

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

and also, to get errors, add this one

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Answer 2

Can you try this?

$query = $conn->prepare('SELECT name, file_loc, img_id, filter, votes FROM images WHERE user_id=? ORDER BY votes DESC LIMIT ?, 10');
$query->bindParam(1, $user_id);
$query->bindParam(2, $skip);
$query->execute();
$result = $query->fetchAll();