Why won't this PDOStatement execute properly?

Question

I have other PDO Statements that execute fine, but this one is screwed up.

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' );
$sth->execute( array( 0, 10 ) );

The above does NOT work, but the below does work:

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT 0,10' );
$sth->execute( array( 0, 10 ) );

So why won't the first way display any of my results when it should be giving the same response?

So here is what I have now

$start = 0;
$perpage = 10;

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' );
$sth->bindValue(1, $start, PDO::PARAM_INT);
$sth->bindValue(2, $perpage, PDO::PARAM_INT);
$sth->execute();

this also does not work

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' );
$sth->bindParam(1, 0, PDO::PARAM_INT);
$sth->bindParam(2, 10, PDO::PARAM_INT);
$sth->execute();

can you change array( 0, 10, ) to array( 0, 10 ) and see if it works — Satya, Sep 20 '12 at 16:09
Sorry, that was a typo. It still doesn't work without the second comma. :( — Dominick, Sep 20 '12 at 16:11

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

The problem is likely that PDO will interpret any inputs as strings. You can try

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT :low,:high' );
$sth->bindValue(':low', 0, PDO::PARAM_INT);
$sth->bindValue(':high', 10, PDO::PARAM_INT);
$sth->execute();

Or

$low = 0;
$high = 10;
$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT :low,:high' );
$sth->bindParam(':low', $low, PDO::PARAM_INT);
$sth->bindParam(':high', $high, PDO::PARAM_INT);
$sth->execute();

Source: How to apply bindValue method in LIMIT clause?

edited May 23 '17 at 12:21

Community

1
1

answered Sep 20 '12 at 16:12

Chris Forrence

9,648
11
43
59

Sounds like it would work but I get this error: Fatal error: Cannot pass parameter 2 by reference. Any ideas? – Dominick Sep 20 '12 at 16:26
You need to change `bindParam` to `bindValue` I believe, in your answer @GlaciesofPacis – PenguinCoder Sep 20 '12 at 17:30
@PenguinCoder - Yep, I do. `bindParam` only works with parameters, while `bindValue` can either use a parameter or a value – Chris Forrence Sep 20 '12 at 17:37
@GlaciesofPacis I tried both snippets of code and neither returns any results. But if I simple change :low,:high to 0,10 it displays fine. This is driving me crazy. – Dominick Sep 20 '12 at 17:40
Just for fun, add a space after the comma, so `SELECT * FROM 'post' LIMIT :low, :high` – Chris Forrence Sep 20 '12 at 17:42
And don't worry @PenguinCoder, I'm a bit thrown off by your problems too! – Chris Forrence Sep 20 '12 at 17:48
@GlaciesofPacis Added the space and still the same result. Can you think of any other way to achieve this? I just want to control the start position and length of results... – Dominick Sep 20 '12 at 17:58
@Dominick Hmm...you could just use `$sth = $dbh->prepare('SELECT * FROM post LIMIT ' . $low . ', ' . $high);` and not bind any parameters. You would NEED NEED ABSOLUTELY NEED to make sure that the $low and $high variables were numbers by using something like intval(). Just as a workaround. – Chris Forrence Sep 20 '12 at 18:02

artragis · Answer 2 · 2012-09-21T17:24:57.053

0

This is because "prepare" and execute with array argument thinks your datas are string by default. So they escape them with ' '. THe problem is that when you deal with limits those quotes trigger error. The solution is bindValue

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT :number OFFSET :start' );
$sth->bindValue("number",10, PDO::PARAM_INT);
$sth->bindValue("start",0,PDO::PARAM_INT);
$sth->execute();

edited Sep 21 '12 at 17:24

answered Sep 20 '12 at 16:11

artragis

3,797
1
15
28

$start = 0; $perpage = 10; $sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' ); $sth->bindValue(1, $start, PDO::PARAM_INT); $sth->bindValue(2, $perpage, PDO::PARAM_INT); $sth->execute(); Still not working – Dominick Sep 20 '12 at 17:05
why do you use 1 and 2 as index? I think 0 and 1 are the good ones. You can also use named parameters (for example :limit and :offset) – artragis Sep 20 '12 at 18:11
@artragis bindValue is actually 1-indexed (For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter). I agree that using named parameters is clearer though! – Chris Forrence Sep 21 '12 at 12:26
if the new code does not work it is that you have misspelled your table name. Check the case of "post", check the singular etc. – artragis Sep 21 '12 at 17:25

score 0 · Answer 3 · edited May 23 '17 at 12:04

Not sure if you saw this question but have you tried casting the values you send as ints?

$start = 0;
$perpage = 10;

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' );
$sth->bindValue(1, (int)$start, PDO::PARAM_INT);
$sth->bindValue(2, (int)$perpage, PDO::PARAM_INT);
$sth->execute();

Or it says to do this:

$start = 0;
$perpage = 10;

$sth = $dbh->prepare( 'SELECT * FROM `post` LIMIT ?,?' );
$sth->bindValue(1, intval($start), PDO::PARAM_INT);
$sth->bindValue(2, intval($perpage), PDO::PARAM_INT);
$sth->execute();

score -1 · Answer 4 · edited May 23 '17 at 12:21

-1

What database? MySQL? SQL Server? Oracle?

In MySQL the PDO with LIMIT clause should work as in GlaciesofPacis's post. However, if you are using SQL SERVER you're not using the correct syntax. Referenced from StackOverflow question:

$query = "
DECLARE @Sort
SET ROWCOUNT :startRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT :pageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
";

$dbh->prepare($query);
$sth->bindParam(':startRow',0, PDO::PARAM_INT);
$sth->bindParam(':pageSize',10, PDO::PARAM_INT);
$sth->execute();

edited May 23 '17 at 12:21

Community

1
1

answered Sep 20 '12 at 17:25

PenguinCoder

4,174
24
37

It's MySQL. GlaciesofPacis' suggestion returns Fatal error: Cannot pass parameter 2 by reference. Any ideas? – Dominick Sep 20 '12 at 17:27
You need to change `bindParam` to `bindValue`. – PenguinCoder Sep 20 '12 at 17:30

Why won't this PDOStatement execute properly?

4 Answers4