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?

found in a quick google search: stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit — G-Nugget
– G-Nugget, Commented Feb 4, 2013 at 19:46

Your Common Sense · Accepted Answer · 2013-02-04 20:13:08Z

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

edited Feb 4, 2013 at 20:13

answered Feb 4, 2013 at 20:05

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kalpesh · Accepted Answer · 2013-02-04 19:47:10Z

0

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

answered Feb 4, 2013 at 19:47

Kalpesh

5,6952 gold badges25 silver badges39 bronze badges

Collectives™ on Stack Overflow

trouble getting mysql rows using limit

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related