How to use the ? placeholder with an SQL statement involving "LIMIT ?"?

Question

I checked this: How do I use pdo's prepared statement for order by and limit clauses?

But it doesn't talk about the "?" placeholder.

I have a statement like this:

$stmt=$connect->prepare("SELECT * FROM users LIMIT ?");
$stmt->execute(array($max_num));

But it doesn't work because the LIMIT number is not supposed to be enclosed with parentheses in the first place.

How can I solve this problem? Thanks in advance.

it's maximum number of rows, `$max_num=$_GET['max_num']`. It's defined by the user. Thanks — alexx0186, May 21 '12 at 19:05
I don't get any error message. But When I put $max_num back into the SQL statement, i get the desired outcome. — alexx0186, May 21 '12 at 19:08
Does it work if you use `$stmt->bindParam(1, $max_num);` instead of `execute`? — Jivings, May 21 '12 at 19:10
Possible duplicate: http://stackoverflow.com/q/2269840/259457 — Travesty3, May 21 '12 at 19:11
Have you also checked the followup question? http://stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit — mario, May 21 '12 at 19:11
Hi, Yes i've seen this but it doesn't discuss the use of `?`. I wanted to know if I can use `?` in my specific case. Thanks — alexx0186, May 21 '12 at 19:14
you can also use execute() with an array and ? placeholders, see http://stackoverflow.com/questions/10437423/how-can-i-pass-an-array-of-pdo-parameters-yet-still-specify-their-types/10438026#10438026 cast the $max_num to an integer though. — goat, May 21 '12 at 19:20

score 2 · Answer 1 · answered May 21 '12 at 20:42

2

    $stmt=$connect->prepare("SELECT * FROM users LIMIT ?");
    $stmt->execute(array($max_num));

The above query is not working because the parameter expects a integer and when you bind it in above fashion, the value is bound to a string.

So, the interpreted query takes the following form :

   select * from users limit '1'

You should modify your query in the following way :

     $stmt->bindValue(1,1,PDO::PARAM_INT);
     $stmt->execute();

answered May 21 '12 at 20:42

sudip

2,781
1
29
41

Hi, thanks for your response. Is the first `1` the index position of the parameter? What if I have `SELECT * FROM users WHERE user=? LIMIT ?` and I have `$stmt->execute(array($user))`. Should it be `$stmt->bindValue(2,$max_num,PDO::PARAM_INT);`? Thanks a lot – alexx0186 May 21 '12 at 20:47
1

yes, you are right. 1 is the index position. $stmt->bindValue(2,1,PDO::PARAM_INT);$stmt->bindValue(1,'username'); and then $stmt->execute(); – sudip May 21 '12 at 20:52

score 1 · Accepted Answer · answered May 21 '12 at 19:30

1

$connect->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt=$connect->prepare("SELECT * FROM users LIMIT ?");
$stmt->execute(array((int)$max_num));

answered May 21 '12 at 19:30

goat

31,486
7
73
96

Hi Chris, thanks for your response. It worked for me. I didn't even need to use the `(int)`. What was the purpose of the setAttribute statement in this case? Thanks a lot. Regards – alexx0186 May 21 '12 at 19:52
Oops I think you logged out. Anyway, please let me know on this as I'm quite intrigued as to why an error handling attribute solved the problem. Thanks – alexx0186 May 21 '12 at 20:37
@alexx0186 I think this link might be relevant. https://michaelseiler.net/2016/07/04/dont-emulate-prepared-statements-pdo-mysql/ – Julian Jan 07 '18 at 09:58

aynber · Answer 3 · 2012-05-21T19:34:08.777

0

Try

$stmt->execute(array((int)$max_num));

Or if that doesn't work, try

$stmt->bindParam(1, (int)$max, PDO::PARAM_INT);
$stmt->execute();

edited May 21 '12 at 19:34

answered May 21 '12 at 19:12

aynber

22,380
8
50
63

Hi, thanks for your response but I couldn't get it to work. It gives me `PHP Fatal error: Cannot pass parameter 2 by reference in ` – alexx0186 May 21 '12 at 19:26
On which example? (BTW, I just noticed a parenthesis mismatch on #1, so I'll fix that) – aynber May 21 '12 at 19:33

score -2 · Answer 4 · answered May 21 '12 at 19:14

-2

It looks like PDO doesn't understand LIMIT to know that its a numeric value. You might have to live with

$connect->query("SELECT * FROM users LIMIT $max_num");

answered May 21 '12 at 19:14

Julian

1,522
11
26

How to use the ? placeholder with an SQL statement involving "LIMIT ?"?

4 Answers4