mysql pdo select,使用PDO有效地获取使用WHERE子句返回SELECT查询的行数

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.