Parametrized Pdo Query and 'Limit' Clause - Not Working

Parametrized PDO query and `LIMIT` clause - not working

I just tested a bunch of cases. I'm using PHP 5.3.15 on OS X, and querying MySQL 5.6.12.

Any combination works if you set:

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

All of the following work: you can use either an int or a string; you don't need to use PDO::PARAM_INT.

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$int = intval(1);
$int = '1';

$stmt->bindValue(1, 1);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1');
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1', PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

You can also forget about bindValue() or bindParam(), and instead pass either an int or a string in an array argument to execute(). This works fine and does the same thing, but using an array is simpler and often more convenient to code.

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->execute(array($int));
print_r($stmt->fetchAll());

$stmt->execute(array($string));
print_r($stmt->fetchAll());

If you enable emulated prepares, only one combination works: you must use an integer as the parameter and you must specify PDO::PARAM_INT:

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

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->bindValue(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

Passing values to execute() doesn't work if you have emulated prepares enabled.

Php pdo and offset=0

Your SQL query is invalid.

You have:

$sql = 'SELECT * FROM journal where LIMIT :limit OFFSET :offset';

As @Jessie Jackson points out, why is the "where" part empty?

The following is valid"

$sql = 'SELECT * FROM journal LIMIT :limit OFFSET :offset';

I don't why you where getting the error that were you getting:

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

The error with the invalid where statement should look something like this:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT ? OFFSET ?' at line 1 in /path/to/stackoverflow/tmp/pdo.php on line 18

And to be absolutely sure, the following test works fine:

<?php

$pdo = new PDO(
    'mysql:dbname=test',
    'yser',
    'pass'
);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$limit = 0;
$offset = 0;

$sql = 'SELECT * FROM journal LIMIT :limit OFFSET :offset';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$res = $stmt->execute();

var_dump($res);
var_dump($stmt->fetchAll());

So, why you are getting that error is nothing to do with $offset=0.

Update

It's possible the reason is if you have PDO::ATTR_EMULATE_PREPARES set to true, read Parametrized PDO query and LIMIT clause - not working.

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 bug when trying to parameterize SQL LIMIT :offset, :display?

Based on Update 2 in the question, where execution stops after the execute statement, it looks like the query is failing. After looking at some PDO documentation, it looks like the default error handling setting is PDO::ERRMODE_SILENT, which would result in the behavior you are seeing.

This is most likely due to the numbers in your LIMIT clause being put into single-quotes when passed in as parameters, as was happening in this post.

The solution to that post was to specify the parameters as integers, using the bindValue method. So you will probably have to do something similar.

And it looks like you should also be executing your queries with try-catch blocks in order to catch the MySQL error.

---

bindValue method:

if ($data !== null)
{
    for ($i=0; $i<count($data); $i++)
        $query->bindValue($i+1, $data[$i], PDO::PARAM_INT);
    $query->execute($data);
}
else
    $query->execute();

Why is my PDO query using LIKE & LIMIT not working?

Update:

$query="SELECT * FROM article WHERE title LIKE ? OR description LIKE ?";
$query.=" LIMIT ?, ?";
$stmt=$cxn->prepare($query);
$stmt->bindValue(1, "%".$keyword."%");
$stmt->bindValue(2, "%".$keyword."%");
$stmt->bindValue(3, $row_number, PDO::PARAM_INT);
$stmt->bindValue(4, $items_per_page, PDO::PARAM_INT);

$stmt->execute();

My PDO Prepared Statement is not working with a fetchALL and Limit

replace the below line

$stmt->execute([$somequantity]);

with

$stmt->bindParam(1, $somequantity, PDO::PARAM_INT);
$stmt->execute();

---

Related Topics