Geek asks and answers

Configuring PDO / MySQL LIMIT with Name Placeholders

I have a problem with part of an LIMITSQL query. This is because the request is passed as a string. I saw another Q here that deals with binding parameters, nothing related to Named Placeholders in the array.

Here is 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 get is: Syntax error or access violation near ''20''(20 is the value $numberOfSlides).

How can i fix this?

+5
source share
2 answers

The problem is that execute() quotes the numbersit is processed as strings:

From the manual, an array of values ​​with as many elements as the associated parameters in the executed SQL statement. All values ​​are treated as PDO :: PARAM_STR.

<?php 
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;
}
?>
+9

:

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

More articles:


All Articles