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

文章目录[隐藏]

PHP PDO and query with 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->dbh 是 PDO 实例)：

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/tech/bigdata/271057.html