0

I'm having trouble binding an INT value to my PDO prepared statement. It's passing the INTs as strings, ''s and all. I need to bind INTs for my LIMIT and OFFSET in the SQL query.

Here's my code: 

$sth = $dbh->prepare("SELECT * FROM post WHERE private = 0 ORDER BY post_time DESC LIMIT :number_results OFFSET :offset");
$sth->execute(array(':number_results' => $number_results, ':offset' => $offset));
$errors = $sth->errorInfo();
    print_r($errors[2]);

I'm receiving You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''20' OFFSET '0'' at line 1

I found this bug report, but have not had any luck so far with the comments. How can I bind these values?

Edit: When I try to set the param type, I don't get any errors, but it doesn't look like it's binding the correct parameters? 

Params:  2
Key: Name: [15] :number_results
paramno=-1
name=[15] ":number_results"
is_param=1
param_type=1
Key: Name: [7] :offset
paramno=-1
name=[7] ":offset"
is_param=1
param_type=1
dcclassics
  • 896
  • 1
  • 12
  • 38
  • You are sure that $number_results is an int that is 20 and not a string that is '20'? Try to cast it to an int – WizKid May 19 '14 at 02:50
  • @WizKid: I did add (int)$number_results as the bind param, but got the same thing. `var_dump($number_results)` is int. – dcclassics May 19 '14 at 02:55

1 Answers1

1

You can bind parameters and specify the data type using bindParam. In your case you want to tell PDO the value is an integer using PDO::PARAM_INT.

$sth = $dbh->prepare("SELECT * FROM post WHERE private = 0 ORDER BY post_time DESC LIMIT :number_results OFFSET :offset");

// Bind params
$sth->bindParam(':number_results', $number_results, PDO::PARAM_INT);
$sth->bindParam(':offset', $offset, PDO::PARAM_INT);

$sth->execute();
$errors = $sth->errorInfo();
    print_r($errors[2]);

Because you've specified the parameters individually, there is no need to pass them through as an array in your $sth->execute(); call.

Alex.Ritna
  • 1,957
  • 2
  • 16
  • 24
  • Thanks for the response. I had tried this before as well, please see my edit to my post. Am I misreading the debug or are the parameters not being set correctly? – dcclassics May 19 '14 at 03:05
  • Nope, I'm an idiot. Forgot to put `$sth->execute();` back in after changing to the bindParam. Sorry for the wasted question. It has been answered elsewhere on the site. Still would be nice to see it fixed. – dcclassics May 19 '14 at 03:32
  • My suggestion would be to bump the bug by posting your PHP and MySQL version details and a snippet of code you ran to replicate the issue so people can see it's still relevant and hopefully prioritise it. Especially with more and more people switching to PDO/MySQLi. – Alex.Ritna May 19 '14 at 03:36