php 准备好的语句 - 行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16726023/
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
Prepared Statements - Number of Rows
提问by Kevin
I'm learning about prepared statements and trying to work with a query that yields multiple rows of results. Right now, I'm just trying to figure out how to determine the number of rows and then make that number display in the html.
我正在学习准备好的语句并尝试使用产生多行结果的查询。现在，我只是想弄清楚如何确定行数，然后将该数字显示在 html 中。
My prepared statement looks like this:
我准备好的声明是这样的：
if($stmt = $mysqli -> prepare("SELECT field1, field2, field3 FROM table WHERE id= ?ORDER BY id ASC"))
{
/* Bind parameters, s - string, b - blob, i - int, etc */
$stmt -> bind_param("i", $id);
$stmt -> execute();
/* Bind results */
$stmt -> bind_result($testfield1, $testfield2, $testfield3);
/* Fetch the value */
$stmt -> fetch();
/* Close statement */
$stmt -> close();
}
I understand that I'm supposed to first save the results, then use num_rows, like this:
我知道我应该先保存结果，然后使用num_rows，如下所示：
$stmt->store_result();
$stmt->num_rows;
However, I'm running and issue with the page bugging out when I put that code in there. I haven't even been able to get to the next step of how to display the number of rows
但是，当我将代码放入那里时，我正在运行并出现页面错误的问题。我什至无法进入如何显示行数的下一步
So, the question is: What am I missing in terms of calculating the number of rows inside the prepared statement, then how would I display it with a <?php echo '# rows: '.$WHATGOESHERE;?>
所以，问题是：在计算准备好的语句中的行数方面我缺少什么，那么我将如何用 <?php echo '# rows: '.$WHATGOESHERE;?>
Thanks!!
谢谢！！
回答by Kevin Pei
num_rowsreturns the number, you have to store it in a variable.
num_rows返回数字，您必须将其存储在变量中。
/*.....other code...*/
$numberofrows = $stmt->num_rows;
/*.....other code...*/
echo '# rows: '.$numberofrows;
So full code should be something like this:
所以完整的代码应该是这样的：
if($stmt = $mysqli -> prepare("SELECT field1, field2, field3 FROM table WHERE id= ? ORDER BY id ASC"))
{
/* Bind parameters, s - string, b - blob, i - int, etc */
$stmt -> bind_param("i", $id);
$stmt -> execute();
/* Bind results */
$stmt -> bind_result($testfield1, $testfield2, $testfield3);
/* Fetch the value */
$stmt -> fetch();
$numberofrows = $stmt->num_rows;
/* Close statement */
$stmt -> close();
}
echo '# rows: '.$numberofrows;
回答by mickmackusa
If you are only interested in the row count instead of the actual rows of data, here is a complete query block that is fitted with error checkpoints and the recommended COUNT(*)call in the SELECT clause.
如果您只对行数而不是实际的数据行感兴趣，这里是一个完整的查询块，它配备了错误检查点和COUNT(*)SELECT 子句中的推荐调用。
if (!$conn = new mysqli("host", "user", "pass", "db")) {
echo "Database Connection Error: " , $conn->connect_error; // don't show this to the public
} else {
if (!$stmt = $conn->prepare("SELECT COUNT(*) FROM `table` WHERE id= ?")) {
echo "Prepare Syntax Error: " , $conn->error; // don't show this to the public
} else {
if (!$stmt->bind_param("s", $id) // if trouble while binding to ? placeholder
|| !$stmt->execute() // or if trouble while executing
|| !$stmt->bind_result($num_rows) // or if trouble while binding to $num_rows
|| !$stmt->fetch()) { // or if trouble while fetching the one row.
echo "Statement Error: " , $stmt->error; // don't show this to the public
}else{
echo $num_rows;
}
$stmt->close(); // no longer need statement
}
$conn->close(); // no longer need connection
}
Or if you want to know the row count before iterating/processing the rows, one way is to lump the entire resultset (multi-dimensional array) into a variable and call count()/sizeof()before iterating.
或者，如果您想在迭代/处理行之前知道行数，一种方法是将整个结果集（多维数组）集中到一个变量中并在迭代之前调用count()/ sizeof()。
if (!$conn = new mysqli("host", "user", "pass", "db")) {
echo "Database Connection Error: " , $conn->connect_error;
} else {
if (!$stmt = $conn->prepare("SELECT field1, field2, field3 FROM table WHERE id= ? ORDER BY id ASC")) {
echo "Prepare Syntax Error: " , $conn->error;
} else {
if (!$stmt->bind_param("s", $id)
|| !$stmt->execute()
|| !$result = $stmt->get_result()) {
echo "Statement Error: " , $stmt->error;
}else{
$resultset = $result->fetch_all(MYSQLI_ASSOC);
echo "<div>Num: " , sizeof($resultset) , "</div>";
foreach ($resultset as $row) {
echo "<div>Row: {$row['field1']} & {$row['field2']} & {$row['field3']}</div>"; // do what you like
}
}
$stmt->close();
}
$conn->close();
}
*I have tested both of the above snippets to be successful on my localhost.
*我已经在我的本地主机上测试了上述两个代码段是否成功。
回答by Devner
This works as of Feb 2020:
这适用于2020 年 2 月：
$number_of_records = $stmt->rowCount();
echo $number_of_records;
From php.net manual:
来自 php.net 手册：
PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.
PDOStatement::rowCount() 返回受 DELETE、INSERT 或 UPDATE 语句影响的行数。
Here is an examplefrom their website:
这是他们网站上的一个例子：
<?php
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();
/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>
The above example will output:
上面的例子将输出：
Return number of rows that were deleted:
Deleted 9 rows.
回答by EvWill
Check out the example #2 here: PHP.net
在此处查看示例 #2： PHP.net
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.
使用 PDO::query() 发出一个 SELECT COUNT(*) 语句，其谓词与您预期的 SELECT 语句相同，然后使用 PDOStatement::fetchColumn() 来检索将返回的行数。然后您的应用程序可以执行正确的操作。
