php 使用 PDO 的行数

Row count with PDO

提问by James

There are many conflicting statements around. What is the best way to row count using PDO in PHP? Before using PDO, I just simply used mysql_num_rows.

周围有许多相互矛盾的说法。在 PHP 中使用 PDO 进行行计数的最佳方法是什么？在使用 PDO 之前，我只是简单地使用了mysql_num_rows.

fetchAllis something I won't want because I may sometimes be dealing with large datasets, so not good for my use.

fetchAll是我不想要的东西，因为我有时可能会处理大型数据集，所以不适合我的使用。

Do you have any suggestions?

你有什么建议吗？

回答by karim79

$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; 
$result = $con->prepare($sql); 
$result->execute([$bar]); 
$number_of_rows = $result->fetchColumn(); 

Not the most elegant way to do it, plus it involves an extra query.

不是最优雅的方法，而且它涉及额外的查询。

PDO has PDOStatement::rowCount(), which apparently does notwork in MySql. What a pain.

PDO有PDOStatement::rowCount()，这显然也没有在MySQL的工作。多么痛苦。

From the PDO Doc:

来自 PDO 文档：

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

对于大多数数据库， PDOStatement::rowCount() 不返回受 SELECT 语句影响的行数。相反，使用 PDO::query() 发出一个 SELECT COUNT(*) 语句，其谓词与您预期的 SELECT 语句相同，然后使用 PDOStatement::fetchColumn() 来检索将返回的行数。然后您的应用程序可以执行正确的操作。

EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:

编辑：上面的代码示例使用了一个准备好的语句，在很多情况下，为了计算行数可能是不必要的，所以：

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); 
echo $nRows;

回答by Chad Birch

As I wrote previously in an answer to a similar question, the only reason mysql_num_rows()worked is because it was internally fetching all the rows to give you that information, even if it didn't seem like it to you.

正如我之前在对类似问题的回答中所写的那样，唯一有效的原因mysql_num_rows()是因为它在内部获取所有行以向您提供该信息，即使您看起来并不喜欢。

So in PDO, your options are:

因此，在 PDO 中，您的选择是：

1. Use MySQL's FOUND_ROWS()function.
2. Use PDO's fetchAll()function to fetch all the rows into an array, then use count()on it.
3. Do an extra query to SELECT COUNT(*), as karim79 suggested.

1. 使用 MySQL 的FOUND_ROWS()功能。
2. 使用 PDO 的fetchAll()函数将所有行提取到一个数组中，然后使用count()它。
3. SELECT COUNT(*)按照 karim79 的建议对 进行额外查询。

回答by Your Common Sense

As it often happens, this question is confusing as hell. People are coming here having two different tasksin mind:

正如经常发生的那样，这个问题令人困惑。人们来到这里有两个不同的任务：

1. They need to know how many rows in the table
2. They need to know whether a query returned any rows

1. 他们需要知道表中有多少行
2. 他们需要知道查询是否返回任何行

That's two absolutely different tasks that have nothing in common and cannot be solved by the same function. Ironically, for neither of themthe actual PDOStatement::rowCount()function has to be used.

这是两个完全不同的任务，没有任何共同点，不能用同一个函数来解决。具有讽刺意味的是，对于它们中的任何一个，都不必使用实际的PDOStatement::rowCount()功能。

Let's see why

让我们看看为什么

Counting rows in the table

计算表中的行数

Before using PDO I just simply used mysql_num_rows().

在使用 PDO 之前，我只是简单地使用了mysql_num_rows().

Means you alreadydid it wrong. Using mysql_num_rows()or rowCount()to count the number of rows in the table is a real disasterin terms of consuming the server resources. A database has to read all the rows from the disk, consume the memory on the database server, then send all this heap of data to PHP, consuming PHP process' memory as well, burdening your server with absolute no reason.
Besides, selecting rows only to count them simply makes no sense. A count(*)query has to be run instead. The database will count the records out of the index, without reading the actual rowsand then only onerow returned.

意味着你已经做错了。使用mysql_num_rows()或rowCount()计算表中的行数在消耗服务器资源方面是一场真正的灾难。数据库必须从磁盘读取所有行，消耗数据库服务器上的内存，然后将所有这些数据堆发送到 PHP，同时消耗 PHP 进程的内存，毫无理由地增加服务器的负担。
此外，只选择行来计算它们根本没有意义。一个count(*)查询必须改为运行。该数据库将数记录列的索引，而不读取实际的行，然后只有一个返回行。

For this purpose the code suggested in the accepted answeris fair.

为此，接受的答案中建议的代码是公平的。

Counting the number rows returned.

计算返回的行数。

The second use case is not as disastrous as rather pointless: in case you need to know whether your query returned any data, you always have the data itself!

第二个用例不是灾难性的，而是毫无意义的：如果您需要知道您的查询是否返回任何数据，您总是拥有数据本身！

Say, if you are selecting only one row. All right, you can use the fetched row as a flag:

假设您只选择一行。好吧，您可以使用获取的行作为标志：

$stmt->execute();
$row = $stmt->fetch();
if (!$row) { // here! as simple as that
    echo 'No data found';
}

In case you need to get many rows, then you can use fetchAll().

如果您需要获取多行，则可以使用fetchAll().

fetchAll()is something I won't want as I may sometimes be dealing with large datasets

fetchAll()这是我不想要的，因为我有时可能会处理大型数据集

Yes of course, for the first use case it would be twice as bad. But as we learned already, just don'tselect the rows only to count them, neither with rowCount()nor fetchAll().

是的，当然，对于第一个用例，它会糟糕两倍。但是正如我们已经了解到的，只是不要只选择行来计算它们，既不使用rowCount()也不使用fetchAll()。

But in case you are going to actually use the rows selected, there is nothing wrong in using fetchAll(). Remember that in a web application you should never select a huge amount of rows. Only rows that will be actually used on a web page should be selected, hence you've got to use LIMIT, WHEREor a similar clause in your SQL. And for such a moderate amount of data it's all right to use fetchAll(). And again, just use this function's result in the condition:

但是，如果您要实际使用选定的行，则使用fetchAll(). 请记住，在 Web 应用程序中，您永远不应选择大量行。只应选择将在网页上实际使用的行，因此您必须在 SQL 中使用LIMIT,WHERE或类似的子句。对于如此中等数量的数据，使用fetchAll(). 同样，只需在条件中使用此函数的结果：

$stmt->execute();
$data = $stmt->fetchAll();
if (!$data) { // again, no rowCount() is needed!
    echo 'No data found';
}

And of course it will be absolute madnessto run an extra queryonly to tell whether your other query returned any rows, as it suggested in the two top answers.

当然，运行一个额外的查询只是为了告诉你的其他查询是否返回了任何行，这绝对是疯狂的，正如它在两个顶级答案中所建议的那样。

Counting the number of rows in a large resultset

计算大型结果集中的行数

In such a rare case when you need to select a real huge amount of rows (in a console application for example), you have to use an unbuffered query, in order to reduce the amount of memory used. But this is the actual case when rowCount()won't be available, thus there is no use for this function as well.

在这种罕见的情况下，当您需要选择真正大量的行（例如在控制台应用程序中）时，您必须使用无缓冲查询，以减少使用的内存量。但这是实际情况下rowCount()不会可用，因此此功能也没有用处。

Hence, that's the only use case when you may possibly need to run an extra query, in case you need to know a close estimate for the number of rows selected.

因此，这是您可能需要运行额外查询的唯一用例，以防您需要知道所选行数的近似估计。

回答by Dan

This is super late, but I ran into the problem and I do this:

这太晚了，但我遇到了问题，我这样做了：

function countAll($table){
   $dbh = dbConnect();
   $sql = "select * from `$table`";

   $stmt = $dbh->prepare($sql);
    try { $stmt->execute();}
    catch(PDOException $e){echo $e->getMessage();}

return $stmt->rowCount();

It's really simple, and easy. :)

这真的很简单，也很容易。:)

回答by Eric Warnke

I ended up using this:

我最终使用了这个：

$result = $db->query($query)->fetchAll();

if (count($result) > 0) {
    foreach ($result as $row) {
        echo $row['blah'] . '<br />';
    }
} else {
    echo "<p>Nothing matched your query.</p>";
}

回答by TenTen Peter

This post is old but Getting row count in php with PDO is simple

这篇文章很旧，但使用 PDO 在 php 中获取行数很简单

$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();

回答by Eric

This is an old post, but getting frustrated looking for alternatives. It is super unfortunate that PDO lacks this feature, especially as PHP and MySQL tend to go hand in hand.

这是一个旧帖子，但在寻找替代方案时感到沮丧。非常不幸的是 PDO 缺少这个特性，尤其是 PHP 和 MySQL 往往齐头并进。

There is an unfortunate flaw in using fetchColumn() as you can no longer use that result set (effectively) as the fetchColumn() moves the needle to the next row. So for example, if you have a result similar to

使用 fetchColumn() 有一个不幸的缺陷，因为当 fetchColumn() 将指针移到下一行时，您不能再（有效地）使用该结果集。例如，如果您的结果类似于

1. Fruit->Banana
2. Fruit->Apple
3. Fruit->Orange

1. 水果->香蕉
2. 水果->苹果
3. 水果->橙色

If you use fetchColumn() you can find out that there are 3 fruits returned, but if you now loop through the result, you only have two columns, The price of fetchColumn() is the loss of the first column of results just to find out how many rows were returned. That leads to sloppy coding, and totally error ridden results if implemented.

如果使用 fetchColumn() 可以发现返回了 3 个水果，但是如果现在循环遍历结果，则只有两列， fetchColumn() 的代价是损失第一列结果只是为了找到返回了多少行。这会导致编码草率，并且如果实施的话会导致完全错误的结果。

So now, using fetchColumn() you have to implement and entirely new call and MySQL query just to get a fresh working result set. (which hopefully hasn't changed since your last query), I know, unlikely, but it can happen. Also, the overhead of dual queries on all row count validation. Which for this example is small, but parsing 2 million rows on a joined query, not a pleasant price to pay.

所以现在，使用 fetchColumn() 您必须实现全新的调用和 MySQL 查询才能获得新的工作结果集。（希望自您上次查询以来没有改变），我知道，不太可能，但它可能会发生。此外，所有行计数验证的双重查询的开销。对于这个例子来说，这很小，但是在一个连接查询上解析 200 万行，这不是一个令人愉快的代价。

I love PHP and support everyone involved in its development as well as the community at large using PHP on a daily basis, but really hope this is addressed in future releases. This is 'really' my only complaint with PHP PDO, which otherwise is a great class.

我喜欢 PHP 并支持参与其开发的每个人以及每天使用 PHP 的整个社区，但我真的希望在未来的版本中解决这个问题。这“真的”是我对 PHP PDO 的唯一抱怨，否则它是一个很棒的类。

回答by csharp newbie

Answering this because I trapped myself with it by now knowing this and maybe it will be useful.

回答这个问题是因为我现在知道了这一点，我被它困住了，也许它会有用。

Keep in mind that you cant fetch results twice. You have to save fetch result into array, get row count by count($array), and output results with foreach. For example:

请记住，您不能两次获取结果。您必须将获取结果保存到数组中，通过 获取行数count($array)，并使用 输出结果foreach。例如：

$query = "your_query_here";
$STH = $DBH->prepare($query);
$STH->execute();
$rows = $STH->fetchAll();
//all your results is in $rows array
$STH->setFetchMode(PDO::FETCH_ASSOC);           
if (count($rows) > 0) {             
    foreach ($rows as $row) {
        //output your rows
    }                       
}

回答by madfish

If you just want to get a count of rows (not the data) ie. using COUNT(*) in a prepared statement then all you need to do is retrieve the result and read the value:

如果您只想获取行数（而不是数据），即。在准备好的语句中使用 COUNT(*) 然后您需要做的就是检索结果并读取值：

$sql = "SELECT count(*) FROM `table` WHERE foo = bar";
$statement = $con->prepare($sql); 
$statement->execute(); 
$count = $statement->fetch(PDO::FETCH_NUM); // Return array indexed by column number
return reset($count); // Resets array cursor and returns first value (the count)

Actually retrieving all the rows (data) to perform a simple count is a waste of resources. If the result set is large your server may choke on it.

实际上检索所有行（数据）以执行简单计数是一种资源浪费。如果结果集很大，您的服务器可能会阻塞。

回答by Reza Neghabi

Have a look at this link: http://php.net/manual/en/pdostatement.rowcount.phpIt is not recommended to use rowCount() in SELECT statements!

看看这个链接： http://php.net/manual/en/pdostatement.rowcount.php不推荐在 SELECT 语句中使用 rowCount() ！