PHP PDO statement passing parameters to LIMIT in a SQL statement doesn't seem to work

Question

For some reason, I can't get this to work. It returns the right amount of rows but the data seems to be empty, no matter what I try:

$page = intval(trim($_GET['p']));
if($page <= 0)
    $page = 1;
$showcount = 15;
$limit_start = (int)(($page-1)*$showcount);

$stmt_scorelist = $dbh->prepare("
    SELECT user_id,min_time 
    FROM scoretable 
    WHERE type_id = :type AND level_id = :level_id 
    GROUP BY user_id 
    ORDER BY MIN(min_time) ASC 
    LIMIT :limit_start, :showcount
");
$stmt_scorelist->bindParam(':type', $type);
$stmt_scorelist->bindParam(':level_id', $level_id);
$stmt_scorelist->bindValue(':limit_start', (int)$limit_start, PDO::PARAM_INT);
$stmt_scorelist->bindValue(':showcount', (int)$showcount, PDO::PARAM_INT);

I've tried to enter numbers explicitly like this:

$stmt_scorelist->bindValue(':limit_start', 0, PDO::PARAM_INT);
$stmt_scorelist->bindValue(':showcount', 15, PDO::PARAM_INT);

It always returns the correct amount of rows, but the data seems to be empty. I've also tried both (int)$var and intval(trim($var)), I've tried to use bindParam() instead of bindValue(), but none works.

The one thing that works, is if I comment the bound values and replace them with pure numbers in the statement, it works just as intended, the right amount of rows and the data returns:

$stmt_scorelist = $dbh->prepare("
    SELECT user_id,min_time 
    FROM scoretable 
    WHERE type_id = :type AND level_id = :level_id 
    GROUP BY user_id 
    ORDER BY MIN(min_time) ASC 
    LIMIT 0, 15
");
$stmt_scorelist->bindParam(':type', $type);
$stmt_scorelist->bindParam(':level_id', $level_id);
//$stmt_scorelist->bindValue(':limit_start', 0, PDO::PARAM_INT);
//$stmt_scorelist->bindValue(':showcount', 14, PDO::PARAM_INT);

What am I doing wrong? I've seen multiple people have the same issues, but none of the solutions they found seems to work for me.

Yep. Like I said, it does return the right amount of rows, it's just that the data seems to be empty — Acegikmo, Dec 13 '12 at 14:02
set `$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` — GBD, Dec 13 '12 at 14:04
Some PDO concerns [posted here](http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not) — Lenin, Dec 13 '12 at 14:11
That actually works, thanks a bunch! Are there any downsides (Performance or security wise) to use that? — Acegikmo, Dec 13 '12 at 14:14
http://stackoverflow.com/questions/2683576/how-do-i-use-pdos-prepared-statement-for-order-by-and-limit-clauses — ethrbunny, Dec 13 '12 at 14:15

score 0 · Accepted Answer · answered Dec 21 '12 at 08:21

0

This solved the issue:

$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

answered Dec 21 '12 at 08:21

Acegikmo

15
8

PHP PDO statement passing parameters to LIMIT in a SQL statement doesn't seem to work

1 Answers1