关于 mysql:PHP PDO 和 SELECT COUNT(*) 查询

PHP PDO and query with SELECT COUNT(*)

我在这里有一个非常奇怪的问题 – 这是 PDO 不能返回 num_rows 与 MySQL 结合使用的一个小解决方法。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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

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

执行代码:

1
2
3
4
5
if (!empty($sql)) {
    try {
        echo $sql."[cc lang="php"]";
        print_r($dataArray);
        echo"

“;
$numrows = $dbh->num_rows($sql, $dataArray);
} 捕捉(PDOException $ex){
// 回声 $ex;
错误($前);
}
//…
[/cc]

输出:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  (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)
1
2
3
4
Array
(
    [:modId] => 18
)

这进入(注意:$this->dbhPDO 实例):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * 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"[cc lang="php"]";
    print_r($i);
    echo"

“;
返回$i->count;
}
[/cc]

它回响:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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)
1
2
3
4
stdClass Object
(
    [count] => 0
)

调用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/**
 * 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 帖子)。帖子示例如下:如何在 LIMIT 子句中应用 bindValue 方法?


1
$statement->bindValue($key, $value);

你试过用 $statement->bindParam 代替吗?

因为结果不同,Sql本身没有问题,但问题要么出在values上,要么出在bind上。


根据评论中的讨论,您现在似乎掌握了足够的信息:您的 PDO 设置正在阻止您的绑定工作。

您说您正在使用该设置来使 LIMIT 工作 – 我猜想专门绑定到 LIMIT。您通常不能这样做,因为绑定仅用于参数值(即在 WHERE 子句中),而 LIMIT 子句不被视为参数。你能用另一种方式重写绑定的 LIMIT 查询吗?

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


原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/271057.html

(0)
上一篇 2022年7月3日
下一篇 2022年7月3日

相关推荐

发表回复

登录后才能评论