参数化的PDO查询和`LIMIT`子句-不起作用 [英] Parametrized PDO query and `LIMIT` clause - not working

问题描述

我有这样的查询:

SELECT imageurl 
FROM entries 
WHERE thumbdl IS NULL 
LIMIT 10;

它与PDO和MySQL Workbench完美配合(根据需要返回10个URL).

It works perfectly with PDO and MySQL Workbench (it returns 10 urls as I want).

但是我尝试使用PDO参数化LIMIT:

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

这将返回空数组.

推荐答案

我刚刚测试了很多案例.我在OS X上使用的是PHP 5.3.15，并在查询MySQL 5.6.12.

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

以下所有工作:您可以使用int或字符串；您不需要使用PDO :: PARAM_INT.

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

您还可以忘掉bindValue()或bindParam()，而是将int或字符串中的数组参数传递给execute().这可以正常工作，并且可以执行相同的操作，但是使用数组更简单，并且通常更便于编码.

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

如果启用模拟准备，则只有一种组合有效:必须使用整数作为参数，并且必须指定PDO :: PARAM_INT:

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

如果您启用了模拟准备功能，则无法将值传递给execute().

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

这篇关于参数化的PDO查询和`LIMIT`子句-不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！