How to check no. of rows returned when using MYSQL

问题:

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.

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?

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

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

NOTE:

This a stripped down version of the original code.

$form_email and $form_pass are originated from form input.

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);
?>

回答1:

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

which you may check with echo mysqli_error($dbc);

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);
}

回答2:

function authenticateUser($email, $password){
    $stmt = $db->prepare("SELECT user_id, first_name, user_level, pass FROM users WHERE email=? and active is null");
    $stmt->bind_param('s', $email);
    $stmt->execute();
    $res = $stmt->get_result();
    if($res->num_rows > 0){
        $hash = $res->fetch_object()->pass;
        if(password_verify($password, $hash)){
            return true;
        }    
    }
    return false;
}

call the function

if(authenticateUser($_POST['email'], $_POST['password'])){
    //do something
}
else{
    echo "Invalid Email/Password";
}