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

问题描述

有关 SO的众多讨论，了解如何获取使用PDO运行 SELECT 查询时返回的行数。虽然大多数人（包括 PHP手册）建议使用两个查询，第一次运行 COUNT（），我还没有看到一个建议如何使用准备语句与 WHERE 子句。

如何最有效率地（在处理和代码行数）运行 COUNT（）使用相同的 WHERE 子句？准备好的查询已经指定了列。 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']; 
} 
  

我看了只添加 COUNT / code>到 SELECT 子句，并且它只是一个查询，但它看起来没有真正好的方法（或不是数据库特定的方式）

另一个解决方案可能是使 fetch c $ c> 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）
 //准备原始查询，绑定它并执行它。 
 $ 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 
 {
 //没有找到行，显示消息
 echo没有找到具有该名称的人。 
} 
  

解决方案

href =http://php.net/pdostatement.rowcount =nofollow> PDOStatement :: rowCount（） 返回结果集。它实际上调用底层的 mysql_num_rows（） C函数来填充值。

这是MySQL的真实情况，但这种行为不能依赖于其他驱动程序（其他人可能支持它，但它不是保证，我不熟悉别人足以说肯定任何一种方式）。但由于你的问题具体涉及MySQL，它应该满足你的目的。

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.";
}

解决方案

When using MySQL, PDOStatement::rowCount() returns the number of rows in the result set. It actually calls the underlying mysql_num_rows() C function to populate the value. No need for multiple queries or any other messing around.

This is true of MySQL, but this behaviour cannot be relied on for other drivers (others may support it but it's not guaranteed, I'm not familiar with others enough to say for sure either way). But since your question regards specifically MySQL, it should serve your purposes.

这篇关于使用PDO有效地获取使用WHERE子句返回SELECT查询的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！