Setting PDO/MySQL LIMIT with Named Placeholders

Question

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?

try using $params["numberOfSlides"] = (intval(trim($numberOfSlides)); — Rohit Choudhary, May 16 '12 at 12:03
exact dup http://stackoverflow.com/questions/10437423/how-can-i-pass-an-array-of-pdo-parameters-yet-still-specify-their-types/10438026#10438026 — goat, May 16 '12 at 13:08

Lawrence Cherone · Accepted Answer · 2012-05-18T05:34:34.370

9

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.

<?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;
}
?>

edited May 18 '12 at 05:34

answered May 16 '12 at 12:11

Lawrence Cherone

46,049
7
62
106

http://www.php.net/manual/en/pdostatement.execute.php#76966 – Lawrence Cherone May 16 '12 at 12:14
Thanks for the detailed explanation, but now I've got another problem: `Strict standards: Only variables should be passed by reference` – Chuck Le Butt May 16 '12 at 12:21
I had to take `intval(trim($numberOfSlides))` out of bindParam, now it works :) – Chuck Le Butt May 16 '12 at 12:25

score 3 · Answer 2 · answered May 16 '12 at 12:05

3

I'd suggest binding the params and forcing their type:

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

answered May 16 '12 at 12:05

Rawkode

21,990
5
38
45

Hello Rawkode, how you going? I got one similar question about PDO Limit Placeholders, is it possible to use the named placeholder to Limit inside the execute? https://stackoverflow.com/questions/72081221/error-to-selecet-when-use-pdo-prepared-limit?noredirect=1#comment127363151_72081221 – Sophie May 02 '22 at 05:44

Setting PDO/MySQL LIMIT with Named Placeholders

2 Answers2

Linked

Related