mysql limit 占位符_使用命名占位符设置PDO / MySQL LIMIT

I'm having an issue binding the LIMIT part of an SQL query. This is because the query is being passed as a string. I've seen another Q here that deals with binding parameters, nothing that deals with Named Placeholders in an array.

Here's my code:

public function getLatestWork($numberOfSlides, $type = 0) {

$params = array();

$params["numberOfSlides"] = (int) trim($numberOfSlides);

$params["type"] = $type;

$STH = $this->_db->prepare("SELECT slideID

FROM slides

WHERE visible = 'true'

AND type = :type

ORDER BY order

LIMIT :numberOfSlides;");

$STH->execute($params);

$result = $STH->fetchAll(PDO::FETCH_COLUMN);

return $result;

}

The error I'm getting is: Syntax error or access violation near ''20'' (20 is the value of $numberOfSlides).

How can I fix this?

解决方案

The problem is that execute() quotes the numbers and treats as strings:

From the manual - An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

public function getLatestWork($numberOfSlides=10, $type=0) {

$numberOfSlides = intval(trim($numberOfSlides));

$STH = $this->_db->prepare("SELECT slideID

FROM slides

WHERE visible = 'true'

AND type = :type

ORDER BY order

LIMIT :numberOfSlides;");

$STH->bindParam(':numberOfSlides', $numberOfSlides, PDO::PARAM_INT);

$STH->bindParam(':type', $type, PDO::PARAM_INT);

$STH->execute();

$result = $STH->fetchAll(PDO::FETCH_COLUMN);

return $result;

}

?>