具有 FOUND_ROWS() 和 WHERE 条件的 PHP Mysql 总行数

Question

我从数据库中获取了 x 行，这些行总计为“totalRows”。从这些行中，我还想仅对结果列等于“win”的行进行总计。我也没有看到带有“totalWins” echo 的答案。

知道我做错了什么吗？

提前致谢。

public static function getList( $numRows=10000, $order="id DESC" ) {
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table
        ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";

$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->execute();
$list = array();

while ( $row = $st->fetch() ) {
  $article = new Article( $row );
  $list[] = $article;
}

// Now get the total number of rows that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );

// Now get the total number of rows WITH CONDITION matched
$sql = "SELECT FOUND_ROWS() AS totalWins WHERE result = 'win'";
$totalWins = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalWins" => $totalWins[0] ) );

Answer 1

您的语句 SELECT FOUND_ROWS() AS totalWins WHERE result = 'win' 将产生错误，而不是结果集。 FOUND_ROWS() 只是给你一个整数，它不会让你访问上一个查询中的表来做进一步的过滤。因为您没有在语句中引用任何表，所以列 result 对 MySQL 是未知的。

您可以在一个查询中获得所需的两个计数，如下所示:

SELECT FOUND_ROWS() AS totalRows, COUNT(*) AS totalWins FROM table WHERE result = 'win';

FOUND_ROWS() 函数将查看上一个查询中的 SQL_CALC_FOUND_ROWS，并返回一个整数。查询的其余部分就像不将 FOUND_ROWS() 的结果作为结果集中的字段包含在内一样工作。

请注意，当您请求一个常量值(如 FOUND_ROWS() 的结果)作为查询中的一个字段时，它将对查询中的所有行重复。另一方面，当您请求聚合函数的结果时(如 COUNT())，您的查询结果将仅限于一行。这些是潜在的“陷阱”——如果不清楚我在这里的意思，请尝试运行这些查询并检查它们的结果集(按顺序):

SELECT COUNT(*), result FROM table LIMIT 1000;
SELECT FOUND_ROWS(), result FROM table LIMIT 1000;

---

您还将 PDO 与已弃用的 mysql_ 扩展 (mysql_escape_string()) 中的函数一起使用；最好坚持使用一个 API 而不是混合和匹配。文档 lists PDO::quote() as an alternative :

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table 
        ORDER BY " . $conn->quote($order) . " LIMIT :numRows";

请注意文档还 makes the following recommendation :

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.