MySQL 使用 pdo 获取行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13195920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
get number of rows with pdo
提问by Smudger
I have a simple pdo prepared query:
我有一个简单的 pdo 准备查询：
$result = $db->prepare("select id, course from coursescompleted where person=:p");
$result ->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rows = $result->fetch(PDO::FETCH_NUM);
echo $rows[0];
the echo seems to be returning the ID value of the record, not the number of records returned by the query?
回声似乎是返回记录的 ID 值，而不是查询返回的记录数？
any idea or explanation for this?
对此有何想法或解释？
采纳答案by Anirudh Ramanathan
PDO::FETCH_NUM:returns an array indexed by column number as returned in your result set, starting at column 0
PDO::FETCH_NUM：返回一个由结果集中返回的列号索引的数组，从第 0 列开始
You aren't fetching the row-count at all.
您根本没有获取行数。
SELECT COUNT(*) FROM coursescompleted where person=:p
This query would return total rows in $rows[0];
此查询将返回总行数 $rows[0];
EDIT:Please see @ray's answer. using count(id)is better than count(*)for InnoDB.
编辑：请参阅@ray 的回答。使用count(id)比count(*)InnoDB更好。
You could get row-count in the following manner, from your earlier query.
您可以通过以下方式从您之前的查询中获取行数。
$row_count = $result->rowCount();
But be warned:
但请注意：
If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.
如果关联 PDOStatement 执行的最后一条 SQL 语句是 SELECT 语句，则某些数据库可能会返回该语句返回的行数。但是，这种行为并不能保证适用于所有数据库，并且不应依赖于可移植应用程序。
回答by Stephen
You've executed a query that returns rows from the database, fetched the first row from the result into a variable and then echo'd the first column of that row.
您已经执行了一个从数据库返回行的查询，将结果中的第一行提取到一个变量中，然后回显了该行的第一列。
If you want to count, do an SQL count()
如果要计数，请执行 SQL count()
$result = $db->prepare("select count(*) from coursescompleted where person=:p");
$result->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rowCount = $result->fetchColumn(0);
echo $rowCount;
回答by Ray
Assuming idis the primary key use:
假设id是主键使用：
SELECT COUNT(id) FROM coursescompleted WHERE person=:p;
Avoid a count(*). If your storage engine is InnoDB (possibly others except MyIsam) you'll take a performance hit.
避免计数(*)。如果您的存储引擎是 InnoDB（可能是除 MyIsam 之外的其他引擎），您的性能会受到影响。
回答by Martin Lyne
Try echo count($rows);as $rowsis an array.
尝试echo count($rows);原样$rows数组。
Edit:
编辑：
To use the results
使用结果
$rows = $result->fetchAll(/* nothing here */);
if(count($rows) > 0) {
// Show results, perhaps using a foreach($rows as $row)
} else {
echo "Sorry, no results found";
}
回答by starleaf1
You can use this
你可以用这个
<?php
$result = $db->prepare("select id, course from coursescompleted where person=:p");
$result ->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rows = $result->rowCount();
echo $rows;
?>
This sometimes does not work on SELECTqueries. But based on personal experience, and since you didn't mention porting it to other database systems, it should work on MySQL.
这有时不适用于SELECT查询。但是根据个人经验，由于您没有提到将其移植到其他数据库系统，因此它应该适用于 MySQL。
More info is in PHP manual here
更多信息在此处的PHP 手册中
