How to use SELECT with LIMIT in PDO?

Question

I am trying to run the query using PDO to get a few rows in descending order.

I am my using MySQL 5.7.27 and PHP 7.2.

$con = new PDO(".....");
$sql1 = "SELECT * FROM `tab` ORDER BY DESC LIMIT :start, :limit";
$sql2 = "SELECT * FROM `tab` ORDER BY DESC LIMIT 0, 2";

$stmt = $con->preapre($sql1);
$stmt->execute(['start'=>0, 'limit'=>2]);
$array = $stmt->fetchAll();
foreach($array as $rows) {
     //Getting empty here
}

sql1 doesn't give me any row. However, sql2 gives me rows. I would like to get rows with sql1. What are the mistakes here?

Answer 1

LIMIT is expecting the offset and count to be integers. The problem you have is that :start and :limit are being typecast as strings.

One simple solution would be to cast your value as an integer first.

$stmt->bindValue(':start', (int) 0, PDO::PARAM_INT); // make sure to keep (int) when passing in a variable.
$stmt->bindValue(':limit', (int) 2, PDO::PARAM_INT);

Answer 2

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();