使用 MySQLi 准备语句时无法获取行数和获取 [英] Cannot get the number of rows and fetch when using MySQLi prepared statement

问题描述

我想从数据库中获取行数，但是当我尝试这样做时，$g_check 变量将等于 0 并且我的代码将回显$sugg_title 消息位于 else 语句中.但是在数据库中有 4 个插入的组，所以 num_rows 属性应该返回 4.

I want to get the number of rows from the database, but when I try to do this the $g_check variable will be equal to 0 and my code will echo the $sugg_title message which is in the else statement. But in the database there are 4 inserted groups so the num_rows property should return 4.

$sql = "SELECT DISTINCT gp.logo, gp.name
        FROM gmembers AS gm
        LEFT JOIN groups AS gp ON gp.name = gm.gname
        WHERE gp.creator != ? AND gm.mname != ? LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$g_check = $stmt->num_rows;
if ($g_check > 0){
  $result = $stmt->get_result();
  while ($row = $result->fetch_assoc()) {
    $agList .= '<a href="group.php?g='.$row["name"].'"><img class="group_margin" src="groups/'.$row["name"].'/'.$row["logo"].'" alt="'.$row["name"].'" title="'.$row["name"].'" width="70" height="70" /></a>';
  }
}else{
  $sugg_title = "You have no group suggestions at the moment. Click ";
  $sugg_title .= '<a href="all_groups.php">here</a> to view all groups.';
}

我将 strore_result() 和 fetch() 函数放在 execute() 之后，但随后我收到此错误消息:致命错误:未捕获的错误:在布尔值上调用成员函数 fetch_assoc()"

I put the strore_result() and the fetch() functions after execute() but then I get this error message: "Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean"

推荐答案

如果要使用mysqli_stmt::$num_rows(即检查prepared statement上的行数)，你需要在执行准备好的语句后使用 $stmt->store_result() 才能检查行数.这意味着在我们检查返回了多少行之前将结果存储到内存中.

If you want to use mysqli_stmt::$num_rows (that is, check the number of rows on the prepared statement), you need to use $stmt->store_result() after executing the prepared statement before being able to check the number of rows. That means that the result is stored into memory before we check how many rows was returned.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$stmt->store_result(); // Need to store the result into memory first
if ($stmt->num_rows) {
    // ...

然而，如果你想使用mysqli_result::$num_rows(在你从语句结果转换的MySQLi-result上)，你需要在执行$result = $stmt->get_result();，并使用$result->num_rows;，如下所示.

However, if you want to use mysqli_result::$num_rows (on the MySQLi-result you convert from the statement result), you need to do that after doing $result = $stmt->get_result();, and use $result->num_rows;, like shown below.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows) {
    while ($row = $result->fetch_assoc()) {
    // ....

最后，他们都应该做同样的事情 - 提供原始准备好的查询返回的行数.

In the end, they should both end up doing the same thing - provide a number of the rows returned by the original prepared query.

注意
请务必注意，您不能在同一语句中使用 store_result() 和 get_result().这意味着在第一个示例中，您不能转换为 mysqli-result 对象(通过使用 get_result()，它允许您使用标准的 fetch_assoc() 方法).由于 store_result() 将结果存储到内存中，get_result() 无需转换，反之亦然.

Note
It's important to note that you cannot use store_result() and get_result() on the same statement. Which means that in the first example, you can not convert to a mysqli-result object (by using get_result(), which allows you to use the standard fetch_assoc() method). As store_result() stores the result into memory, there are nothing for get_result() to convert, and vice-versa.

这意味着如果你使用store_result()，你需要通过statement-fetch，mysqli_stmt::fetch()来获取，并通过绑定结果>mysqli_stmt::bind_result().如果您使用 get_result()，您应该检查结果 MySQLi-result 对象上的行数(如第二个示例所示).

This means that if you use store_result(), you need to fetch through the statement-fetch, mysqli_stmt::fetch() and bind the results though mysqli_stmt::bind_result(). If you use get_result(), you should check the number of rows on the resulting MySQLi-result object (as shown in the second example).

因此，您应该构建您的代码，以便您只需要使用其中之一.

You should therefor construct your code such that you only need to use one of them.

话虽如此，使用 affected_rows 就像评论中建议的那样，并不是适合这项工作的工具 - 根据 mysqli_stmt::$affected_rows 上的手册(同样的事情适用于常规查询，mysqli::$affected_rows):

That being said, using affected_rows like suggested in the comments, isn't the right tool for the job - as per the manual on mysqli_stmt::$affected_rows (same thing applies for a regular query, mysqli::$affected_rows):

返回受 INSERT、UPDATE 或 DELETE 查询影响的行数.
此函数仅适用于更新表的查询.为了从 SELECT 查询中获取行数，请改用 mysqli_stmt_num_rows().

Returns the number of rows affected by INSERT, UPDATE, or DELETE query.
This function only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

• PHP.net mysqli_stmt::store_result()
• PHP.net mysqli_stmt::get_result()
• PHP.net mysqli_stmt::$num_rows

这篇关于使用 MySQLi 准备语句时无法获取行数和获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！