Using prepared statements to select from a database with a limit tag

Question

I'm using a prepared statement to access some rows of my database, but I'm hitting an error when I specify a limit tag - I'm not sure why.

This is in my model:

public function returnData() {
 $this->limit = 10;

$r = $this->db->select("SELECT id,userid,dateTime,rating,description FROM table where userid = :userid  ORDER BY dateTime ASC LIMIT :limit", array(':userid' => $this->userid, ':limit' => $this->limit));      
    return $this->_returnData($r);
}

Everything works if I replace :limit with a number as below, but I'm not sure why I can't use the prepared statement here - I wondered if it was something to do with quotations on a number, but I can't work it out.

    ORDER BY dateTime ASC LIMIT 10", array(':userid' => $this->userid));

It accesses the following function:

/*
*Select
*@param string $sql - An SQL string
*@param array $array - An array of parameters to bind. Default is empty array
*@param constant $fetchMode - A PDO fetchmode. Default is fetch_assoc
*return mixed
*/
public function select($sql, $array = array(), $fetchMode = PDO::FETCH_ASSOC) {

    $sth = $this->prepare($sql);
        foreach ($array as $key => $value) {    
            $sth->bindValue("$key", $value);
        }

    $sth->execute();
    $this->rowCount = $sth->rowCount(); 
    return $sth->fetchAll($fetchMode);      
}

possible duplicate of [Setting PDO/MySQL LIMIT with Named Placeholders](http://stackoverflow.com/questions/10617894/setting-pdo-mysql-limit-with-named-placeholders) — Sean, Nov 13 '14 at 02:49

score 0 · Accepted Answer · answered Nov 13 '14 at 02:48

For a limit, you will have to pass the value as an integer when binding it :

foreach ($array as $key => $value) {
    if($value == (int)$value)
        $sth->bindValue("$key", $value, PDO::PARAM_INT);
    else
        $sth->bindValue("$key", $value);
}

Using prepared statements to select from a database with a limit tag

1 Answers1