无法将INT绑定到PDO为LIMIT和OFFSET准备的语句[重复]

Question

This question already has an answer here:

• How to apply bindValue method in LIMIT clause? 10 answers

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

</div>

Answer 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.