使用PDO有效地获取带有WHERE子句的SELECT查询返回的行数(Efficiently getting number of rows returned of SELECT query with WHERE clause using PDO)

 关于如何在使用PDO运行SELECT查询时获取返回的行数，有很多关于SO的讨论 。 虽然大多数（包括PHP手册 ）建议使用两个查询，第一个运行COUNT() ，但我没有看到一个建议如何使用带有WHERE子句的预处理语句轻松完成此操作。 
 
 我如何最有效地（在处理和代码行数中COUNT()使用相同的WHERE子句运行COUNT() ？ 准备好的查询已经指定了列。 fetchAll()在这里不起作用，因为它不会缩放; 如果我必须返回数百万行，使用fetchAll处理它将会非常慢。 
 
 例如，没有计数： 
 
$sql = "SELECT
            FirstName,
            LastName
        FROM
            People
        WHERE
            LastName = :lastName";
$query = $pdoLink->prepare($sql);
$query->bindValue(":lastName", '%Smith%');
$query->execute();      
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo $row['FirstName'] . " " . $row['LastName'];
}
 
 我看着只是向SELECT子句添加COUNT(ID) ，并让它只是一个查询，但看起来没有真正的好方法（或者不是特定于数据库的方式fetch()一旦我得到一个倒带fetch()从它排。 
 
 另一个解决方案可能是使WHERE子句成为自己构建的变量。 但是，这似乎不是很有效。 它正在准备两个查询，重新绑定值并执行它。 
 
 所以类似于： 
 
$whereClause = " WHERE
                   LastName = :lastName";

$rowsSql = "SELECT
            COUNT(ID) As NumOfRows
        FROM
            People " . $whereClause;
$rowsQuery = $pdoLink->prepare($sql);
$rowsQuery->bindValue(":lastName", '%Smith%');
$rowsQuery->execute();
if ($rowsQuery->fetchColumn() >= 1)
    //Prepare the original query, bind it, and execute it.
    $sql = "SELECT
                FirstName,
                LastName
            FROM
                People " . $whereClause;
    $query = $pdoLink->prepare($sql);
    $query->bindValue(":lastName", '%Smith%');
    $query->execute();      
    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
        echo $row['FirstName'] . " " . $row['LastName'];
    }
}
else
{
    //No rows found, display message
    echo "No people found with that name.";
}

There are numerous discussions on SO regarding how to get the number of rows returned when running a SELECT query using PDO. While most (including the PHP manual) suggest using two queries, with the first running COUNT(), I haven't seen one that suggested how to easily do this using prepared statements with WHERE clauses.
 
How do I most-efficiently (both in processing and number of lines of code) run a COUNT() using the same WHERE clause? The prepared query already has the columns specified. fetchAll() won't work here because that won't scale; if I have to return millions of rows, processing it using fetchAll would be super slow.
 
For example, without the count:
 
$sql = "SELECT
            FirstName,
            LastName
        FROM
            People
        WHERE
            LastName = :lastName";
$query = $pdoLink->prepare($sql);
$query->bindValue(":lastName", '%Smith%');
$query->execute();      
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo $row['FirstName'] . " " . $row['LastName'];
}
 
I looked at just adding COUNT(ID) to the SELECT clause, and having it be just one query, but it looks like there is no real good way (or not database-specific way) of rewinding the fetch() once I get a row from it.
 
Another solution could be making the WHERE clause it's own variable that is built. But, that doesn't seem very efficient. It's preparing two queries, binding the values all over again, and executing it.
 
So something like:
 
$whereClause = " WHERE
                   LastName = :lastName";

$rowsSql = "SELECT
            COUNT(ID) As NumOfRows
        FROM
            People " . $whereClause;
$rowsQuery = $pdoLink->prepare($sql);
$rowsQuery->bindValue(":lastName", '%Smith%');
$rowsQuery->execute();
if ($rowsQuery->fetchColumn() >= 1)
    //Prepare the original query, bind it, and execute it.
    $sql = "SELECT
                FirstName,
                LastName
            FROM
                People " . $whereClause;
    $query = $pdoLink->prepare($sql);
    $query->bindValue(":lastName", '%Smith%');
    $query->execute();      
    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
        echo $row['FirstName'] . " " . $row['LastName'];
    }
}
else
{
    //No rows found, display message
    echo "No people found with that name.";
}

原文：https://stackoverflow.com/questions/15887692