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

问题描述

我想从数据库中获取行数，但是当我尝试执行此操作时，$g_check变量将等于0，并且我的代码将回显else中的$sugg_title消息. >声明.但是在数据库中有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.';
}

我在execute()之后放置了strore_result()和fetch()函数，但是随后收到以下错误消息:"致命错误:未捕获的错误:在布尔值上调用成员函数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(即检查已准备好的语句中的行数)，则在执行在可以检查行数之前准备好的语句.这意味着在我们检查返回多少行之前，将结果存储到内存中.

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 = $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()，则需要通过语句-fetch获取mysqli_stmt::fetch()，并通过mysqli_stmt::bind_result()绑定结果.如果使用get_result()，则应检查生成的MySQLi结果对象上的行数(如第二个示例所示).

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.

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

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