How to Apply Bindvalue Method in Limit Clause

How to apply bindValue method in LIMIT clause?

I remember having this problem before. Cast the value to an integer before passing it to the bind function. I think this solves it.

$fetchPictures->bindValue(':skip', (int) trim($_GET['skip']), PDO::PARAM_INT);

PHP PDO bindValue in LIMIT

The entire foreach here is a NOOP since passing the variables on execute overwrites this.

foreach($values as $key => $value) {
    $paramDataType = $this->getPdoParamDataType($value);
    $sth->bindValue($key,$value,$paramDataType);
}
$sth->execute($values); // this makes the previous loop a NOOP.

I would recommend you delete it and also do not try to explicitly set the data type. PDO will infer the correct type.

Also, I bet that if you echo $sql right before preparing it, the syntax error will be obvious.

How to use SELECT with LIMIT in PDO?

You cannot use parameters for the values in a LIMIT range in MySQL. One workaround might be to use row number, and then restrict to a range of values:

SELECT *
FROM
(
    SELECT t.*,
        @rn := @rn + 1 AS rn
    FROM tab t,
       (SELECT @rn := 0) r
) t
WHERE rn BETWEEN :start AND :end;

For example, if you wanted the 4th and 5th records, you would use:

$stmt = $con->prepare($sql1);
$stmt->execute(['start'=>4, 
'end'=>5]);
$array = $stmt->fetchAll();

mysql query LIMIT use attribute :variable

You need do it like below (changes are commented):-

$limit = $request->getAttribute('limit');
$sql = "SELECT * FROM users WHERE status = 1 ORDER BY date DESC LIMIT :limit";

try{
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql); //prepare sql first
    $stmt->bindValue(':limit',(int)$limit,PDO::PARAM_INT);//tell value is integer
    $users = $stmt->fetchAll(PDO::FETCH_OBJ);

    if(count($users) ==0) {
        $response->getBody()->write('{"error":{"message":"No More Users Exist"}}');
    } else {
        $response->getBody()->write(json_encode($users));
    }
    $db = null;
} catch (PDOException $e) {
   print "Error!: " . $e->getMessage() . "<br/>";
   die();
}

Using PDO insert values in the limit clause of an SQL statement?

See PHP PDO bindValue in LIMIT

Basically, you need to cast the limit value to int using intval() when binding.

PDO mysql LIMIT with placeholder in an array

Instead of calling execute() with an array argument, you should be able to loop over $param and bind the values as INT one at a time before executing.

$stmt = $ulink->prepare($sql);
$i = 1;
foreach ($param as $value) {
    $stmt->bindValue($i++, $value, PDO::PARAM_INT);
}
$stmt->execute();

Error while using PDO prepared statements and LIMIT in query

Regarding to post LIMIT keyword on MySQL with prepared statement , the code below could solve my problem.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Thanks Álvaro G. Vicario and Maerlyn

---

Related Topics