When using PDO::bindParam my query returns no results

Question

I am doing the following query. If i put the numbers straight into the query the query returns lots of results... Now if I use the bindParam to pass the values there are no results.

I've tested whether or not the passed values have values and the echo shows that they do... so I have no idea why this is happening

Could anyone tell me what am i doing wrong?

 public function searchWithPagination( $startPage = 0, $numberResultsPerPage = 10 ) {

$q = $this->db->prepare( 'SELECT * FROM ecm LIMIT :startpage, :numberresultsperpage' );
$q->bindParam(':startpage', $startPage);
$q->bindParam(':numberresultsperpage', $numberResultsPerPage);
$q->execute();
echo $numberResultsPerPage . $startPage ;
$f = $q->fetchAll();
var_dump($f);
}

EDIT: tried PDO::PARAM_INT still doesnt work

Try bindValue instead. In the user submitted notes in the PHP manual (http://www.php.net/manual/en/pdostatement.bindvalue.php) there is a note about bindParam passing by reference. — GarethL, Jun 13 '13 at 11:00
First, PDO::PARAM_INT **[does work](http://stackoverflow.com/a/15991623/285587)**. Second, [PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/a/15990858/285587) — Your Common Sense, Jun 13 '13 at 11:01
@GarethL that made it work! thanks. please post it as an answer — Jonathan Thurft, Jun 13 '13 at 11:01
@GarethL it is not the issue (Unless opening poster didn't provided a **real** code) — Your Common Sense, Jun 13 '13 at 11:03
I've posted as an answer, but I agree with @YourCommonSense that probably this is more a workaround than a proper answer. I think you can make it work with bindParam. — GarethL, Jun 13 '13 at 11:04

score 1 · Accepted Answer · answered Jun 13 '13 at 11:03

1

Try using bindValue instead of bindParam. In the user submitted notes in the PHP manual (php.net/manual/en/pdostatement.bindvalue.php) there is a note about bindParam passing by reference, whereas bindValue doesn't.

answered Jun 13 '13 at 11:03

GarethL

1,473
1
15
16

just bindValue didnt work. I also had to include the type cast (int)$variable for some reason. you might want to change your answer to be fully compliant – Jonathan Thurft Jun 13 '13 at 14:24

score 0 · Answer 2 · answered Jun 13 '13 at 10:55

0

The limit parameters has to be bound as integers, the default binding is string.

$q->bindParam(':startpage', $startPage, PDO::PARAM_INT);
$q->bindParam(':numberresultsperpage', $numberResultsPerPage, PDO::PARAM_INT);

answered Jun 13 '13 at 10:55

Musa

96,336
17
118
137

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

As it is stated in another question/answer:

How to apply bindValue method in LIMIT clause?

You need to bind the params explicitly as INT's, also you should cast them as integers.

$q->bindParam(':numberresultsperpage', (int)$numberResultsPerPage, PDO::PARAM_INT);

edited May 23 '17 at 11:50

Community

1
1

answered Jun 13 '13 at 10:58

Royal Bg

6,988
1
18
24

I tried that and I get another error saying `Cannot pass parameter 2 by reference in` – Jonathan Thurft Jun 13 '13 at 11:00
Another comment there says it depends on the PHP version, i.e. for 5.3 you should use (intval) . Also you should be using bindValue – Royal Bg Jun 13 '13 at 11:05

score 0 · Answer 4 · answered Jun 13 '13 at 11:10

The problem is with your question.

In the code that actually runs, you are binding constant values

$q->bindParam(':startpage', 0);
$q->bindParam(':numberresultsperpage', 10);

which causes the error you mention:

Cannot pass parameter 2 by reference

But in the code you posted here you are binding variables

$q->bindParam(':startpage', $startPage);
$q->bindParam(':numberresultsperpage', $numberResultsPerPage);

which works all right, if fed with PDO::PARAM_INT or if emulation code is turned off.

When using PDO::bindParam my query returns no results

4 Answers4