I have query like this:
SELECT imageurl
FROM entries
WHERE thumbdl IS NULL
LIMIT 10;
It works perfectly with PDO and MySQL Workbench (it returns 10 urls as I want).
However I tried to parametrize LIMIT
with PDO:
$cnt = 10;
$query = $this->link->prepare("
SELECT imageurl
FROM entries
WHERE imgdl is null
LIMIT ?
");
$query->bindValue(1, $cnt);
$query->execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);
That returns empty array.
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.
By default bindValue
binds a string value but limit is an integer, so use PDO::PARAM_INT
$query->bindValue(1, $cnt, PDO::PARAM_INT);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With