PHP PDO 和使用 SELECT COUNT(*) 查询

Question

我这里有一个非常奇怪的问题 - 这是 PDO 无法返回 num_rows 与 MySQL 结合的事实的一个小解决方法。

我可以通过 phpmyadmin 将此查询直接提供给数据库:

SELECT COUNT(*) AS COUNT
FROM ((
         (SELECT 'Key' AS tradeOrigin,
                 CONCAT(skti.tier, ' ', skty.type) AS trade,
                 CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 1
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = 18
          JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
          JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
       UNION ALL
         (SELECT 'Mod' AS tradeOrigin,
                 sm.name AS trade,
                 CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 2
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = 18
          JOIN mods sm ON t.sourceModId = sm.modId)) AS derived)

它会按 预期 返回包含 count 列和 1 值的一行。

但是当它需要通过我的框架时就出错了。

执行代码:

if (!empty($sql)) {
    try {
        echo $sql."<br><pre>";
        print_r($dataArray);
        echo "</pre>";
        $numrows = $dbh->num_rows($sql, $dataArray);
    } catch (PDOException $ex) {
//        echo $ex;
        error($ex);
    }
    //...

输出:

  (SELECT 'Key' AS tradeOrigin,
          CONCAT(skti.tier, ' ', skty.type) AS trade,
          CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 1
   AND t.destinationItem = 1
   AND t.userId = u.userId
   AND t.sourceModId = :modId
   JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
   JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
UNION ALL
  (SELECT 'Mod' AS tradeOrigin,
          sm.name AS trade,
          CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 2
   AND t.destinationItem = 1
   AND t.userId = u.userId
   AND t.sourceModId = :modId
   JOIN mods sm ON t.sourceModId = sm.modId)

---

Array
(
    [:modId] => 18
)

这进入(注意:$this->dbh 是一个 PDO 实例):

/**
 * Returns the number of rows that this query has.
 * 
 * @param type $query   The input query
 * @param type $values  The values
 * @return type Number of rows
 */
public function num_rows($query, $values) {
    $newquery = "SELECT COUNT(*) AS count FROM (({$query}) AS derived)";
    echo $newquery;
    $statement = $this->query($newquery, $values);
    $i = $statement->fetch();
    echo "<pre>";
    print_r($i);
    echo "</pre>";
    return $i->count;
}

它呼应:

SELECT COUNT(*) AS COUNT
FROM ((
         (SELECT 'Key' AS tradeOrigin,
                 CONCAT(skti.tier, ' ', skty.type) AS trade,
                 CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 1
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = :modId
          JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
          JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
       UNION ALL
         (SELECT 'Mod' AS tradeOrigin,
                 sm.name AS trade,
                 CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 2
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = :modId
          JOIN mods sm ON t.sourceModId = sm.modId)) AS derived)

---

stdClass Object
(
    [count] => 0
)

调用:

/**
 * Can be called to create a query. Use either unnamed or named placeholders for the prepared statements.
 * 
 * Example: $dbh->query("INSERT INTO table (data1, data2) VALUES(?, ?)", array($data1, $data2));
 * 
 * @param type $query   The input query, including unnamed or named placeholders
 * @param type $values  The input values. If it's not an array, then it will be an one-element array
 * @return type The statement constructed by this query
 */
public function query($query, $values = array()) {
    if (!is_array($values)) {
        $values = array($values);
    }
    $statement = $this->dbh->prepare($query);
    $statement->setFetchMode(PDO::FETCH_OBJ);
    if (is_assoc($values)) {
        foreach ($values as $key => $value) {
            $statement->bindValue($key, $value);
        }
    }
    else {
        $i = 1;
        foreach ($values as $value) {
            $statement->bindValue($i++, $value);
        }
    }
    $statement->execute();
    return $statement;
}

query 方法在过去已被证明有效，而且奇怪的是 num_rows 确实适用于一些其他任意查询，正确返回 6 作为计数。

我真的被困在这里，不知道发生了什么，请帮帮我。

更新:

显然，一个设置向我介绍了这个问题:$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);。

但是我仍然想知道为什么它会导致问题，因为我不能只是禁用它，因为我将它用于 LIMIT 子句，否则会失败(关于此事的大量 SO 帖子)。帖子示例如下:How to apply bindValue method in LIMIT clause?

Answer 1

经过评论中的讨论，您现在似乎有足够的信息可以继续:您的 PDO 设置阻止您的绑定(bind)工作。

您说您正在使用该设置来使 LIMIT 正常工作 - 我猜测是专门绑定(bind)到 LIMIT。你通常不能这样做，因为绑定(bind)只是针对参数值(即在 WHERE 子句中)，而 LIMIT 子句不被视为参数。你能用另一种方式重写绑定(bind)的 LIMIT 查询吗？

最后，根据评论，确保您使用的任何别名都是小写的，并且不是保留字。您可能可以不区分大小写地使用这些，但代码约定无论如何都有好处!