如何检查编号.使用MYSQLI_STMT_PREPARE和MYSQLI_FETCH_ARRAY时返回的行数? [英] How to check no. of rows returned when using MYSQLI_STMT_PREPARE and MYSQLI_FETCH_ARRAY?

问题描述

我认为我可以使用MYSQLI_STMT_NUM_ROWS和MYSQLI_STMT_STORE_RESULT来检查是否存在.返回的行数. (请参阅带注释的行///1/1///，///2///，///3///)

I thought I could use MYSQLI_STMT_NUM_ROWS and MYSQLI_STMT_STORE_RESULTto check for no. of rows returned. (see commented lines ///1///, ///2///, ///3///)

但是在下面的上下文中似乎没有.

But it doesn't seem to in the context below.

此代码确实有效(没有注释行)，但是我试图添加额外的检查，以确认返回的记录不超过1条. (尽管应该总是这样，因为表中的email字段是唯一的，但是无论如何检查都不会受到损害.)

This codes does work (without the commented lines), but I am trying to add an extra check, to confirm that no more than 1 record is returned. (even though this should always be the case, as the email field in the table is unique, but it doesn't hurt to do the check anyway).

任何人都可以阐明我在做什么错吗?

Can anyone shed some light on what I'm doing wrong?

这是我得到的错误(如果WHILE ...行，则出现在第86行):

This is the error I get below (line 86 if the WHILE ... line):

第86行上的脚本'L:\ includes \ login_functions.inc.php'中发生错误:mysqli_fetch_array()期望参数1为mysqli_result，给定布尔值

An error occurred in script 'L:\includes\login_functions.inc.php' on line 86: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

注意:

这是原始代码的精简版.

This a stripped down version of the original code.

$form_email和$form_pass源自表单输入.

代码是程序性的，因为我喜欢那样.

Code is procedural, because I like it that way.

<?php
// Prepared statement.
$prep_sel = 'SELECT user_id, first_name, user_level, pass FROM users WHERE email=? and active is null';
// Initialise connection.
$stmt_sel = mysqli_stmt_init($dbc);
// Check if there are any DB connection problems.
....
// Prepare statement, bind parameters (an integer and a string) and execute the statement
if (mysqli_stmt_prepare($stmt_sel, $prep_sel)) {
     mysqli_stmt_bind_param($stmt_sel, 's', $form_email);
     mysqli_stmt_execute($stmt_sel);
     ///1///mysqli_stmt_store_result($stmt_sel);
}

///2///if (mysqli_stmt_num_rows($stmt_sel) == 1) { // one record found.
     // Get the results.
     $result = mysqli_stmt_get_result($stmt_sel);
     while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
          // Now check if the passwords match.
          if (password_verify($form_pass, $row['pass'])) {
              return array(true, $row);
          } else {
              $errors[] = 'the details you provided does not match our records';
              $errors[] = 'your account has not been activated';
          }
      }
///3///}
/* close statement */
mysqli_stmt_close($stmt_sel);
?>

推荐答案

在调用mysqli_stmt_store_result()之后，MySQL驱动程序将不允许您对结果集进行操作，直到获取所有行或释放结果集并声明为止关闭.因此，随后对mysqli_stmt_get_result()的调用将返回false，并可能导致类似

After calling mysqli_stmt_store_result(), the MySQL driver will not permit you to operate on a result set until all rows are fetched or the result set is freed and the statement closed. So a subsequent call to mysqli_stmt_get_result() will return false, and probably result in an error like

命令不同步；您现在不能运行此命令

Commands out of sync; you can't run this command now

您可以通过echo mysqli_error($dbc);

使用mysqli_stmt_get_result()转移语句的结果集将使您可以访问其num_rows属性，因此实际上不需要使用mysqli_stmt_store_result().相反，仅在检查返回的行数之前依靠mysqli_stmt_get_result():

Transferring the statement's result set with mysqli_stmt_get_result() will give you access to its num_rows property, so you actually don't need to use mysqli_stmt_store_result(). Instead just rely on mysqli_stmt_get_result() before checking the number of rows returned:

if (mysqli_stmt_prepare($stmt_sel, $prep_sel)) {
     mysqli_stmt_bind_param($stmt_sel, 's', $form_email);
     mysqli_stmt_execute($stmt_sel);

     // Transfer the result set here:
     $result = mysqli_stmt_get_result($stmt_sel);

     // Then check rows returned on the $result obj
     // using mysqli_num_rows(), not mysqli_stmt_num_rows()
     if (mysqli_num_rows($result) == 1) {
       while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
          // Check your password, etc....
       }
     }
     else {
        // More than 1, do whatever you need to handle this
     }

     // Close it
     mysqli_stmt_close($stmt_sel);
}

这篇关于如何检查编号.使用MYSQLI_STMT_PREPARE和MYSQLI_FETCH_ARRAY时返回的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！