mysqli_fetch_assoc not working with mysqli_stmt_store_result

Question

I have a PHP file which gets JSON formatted data via a POST request.

I'm using prepared statements and everything works fine until calling mysqli_fetch_assoc

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\LoanAppTest\login.php on line 31

This only happens when I declare mysqli_stmt_execute($stmt);, if I comment it out then it would get me the result that I want however I need to have mysqli_stmt_execute($stmt); so that $count = mysqli_stmt_num_rows($stmt); could work. This is my code:

if($jsonobj == null){
    header("Location: http://localhost/dashboard");
} else {
      include "config.php";
      $obj = json_decode($jsonobj, true);
      $clientID = (int)$obj['prop_ClientID'];

      $query = "SELECT ClientID, FirstName, LastName, MiddleName, ContactNo FROM tblClient WHERE ClientID = ?";

      // Initialize prepared statement
      $stmt = mysqli_stmt_init($con);

      if (!mysqli_stmt_prepare($stmt, $query)) {
        echo "SQL Statmenet Failed.";
      } else {
        // Bind parameters and execute statement
        mysqli_stmt_bind_param($stmt, "i", $clientID);
        mysqli_stmt_execute($stmt);

        // Store result and count for validation (check if ClientID exists)
        mysqli_stmt_store_result($stmt);
        $count = mysqli_stmt_num_rows($stmt);
        if ($count == 0) {
            echo "No Client ID in DB";
        } else {
            $result = mysqli_stmt_get_result($stmt);
            $row = mysqli_fetch_assoc($result); // This part returns the error
            print_r($row);
        }
      }
}

Answer 1

To retrieve the number of rows and a result set, you should only use one method of result retrieval, mysqli_stmt_get_result(mysqli_stmt), mysqli_fetch_assoc(mysqli_result) and mysqli_num_rows(mysqli_result)
Or
mysqli_stmt_store_result(mysqli_stmt), mysqli_stmt_fetch(mysqli_stmt) and mysqli_stmt_num_rows(mysqli_stmt).

Effectively the issue is caused by mysqli_stmt_store_result, which moves the result-set into memory, to act as a buffer for subsequent calls to mysqli_stmt_fetch(mysqli_stmt), which would then pull the record from the buffer, instead of mysqli_fetch_assoc(mysqli_result).

mysqli_stmt_store_result

// Initialize prepared statement
$stmt = mysqli_stmt_init($con);
if (!mysqli_stmt_prepare($stmt, $query)) {
    echo 'SQL Statmenet Failed.';
} else {
    // Bind parameters and execute statement
    mysqli_stmt_bind_param($stmt, 'i', $clientID);
    if (mysqli_stmt_execute($stmt)) {
        //bind results to row array
        mysqli_stmt_bind_result($stmt, $row['ClientID'], $row['FirstName'], $row['LastName'], $row['MiddleName'], $row['ContactNo']);

        // Store result and count for validation (check if ClientID exists)
        if (mysqli_stmt_store_result($stmt)) {
            $count = mysqli_stmt_num_rows($stmt);
            if (0 === $count) {
                echo 'No Client ID in DB';
            } else {
                mysqli_stmt_fetch($stmt);
                print_r($row);
            }
            mysqli_stmt_free_result($stmt);
        }
    }
}
mysqli_stmt_close($stmt);

mysqli_stmt_get_result

// Initialize prepared statement
$stmt = mysqli_stmt_init($con);
if (!mysqli_stmt_prepare($stmt, $query)) {
    echo 'SQL Statmenet Failed.';
} else {
    // Bind parameters and execute statement
    mysqli_stmt_bind_param($stmt, 'i', $clientID);
    mysqli_stmt_execute($stmt);

    // Store result and count for validation (check if ClientID exists)
    if ($result = mysqli_stmt_get_result($stmt)) {
        $count = mysqli_num_rows($result);
        if (0 === $count) {
            echo 'No Client ID in DB';
        } else {
            $row = mysqli_fetch_assoc($result);
            print_r($row);
        }
        mysqli_free_result($result);
    }
}
mysqli_stmt_close($stmt);

---

However, mysqli_stmt_num_rows or mysqli_num_rows are not needed in this instance, as mysqli_fetch_assoc will return null when no results are available, so you can replace it with the below.

// Initialize prepared statement
$stmt = mysqli_stmt_init($con);
if (!mysqli_stmt_prepare($stmt, $query)) {
    echo 'SQL Statmenet Failed.';
} else {
    // Bind parameters and execute statement
    mysqli_stmt_bind_param($stmt, 'i', $clientID);
    mysqli_stmt_execute($stmt);

    // Store result and count for validation (check if ClientID exists)
    if ($result = mysqli_stmt_get_result($stmt)) {
        if ($row = mysqli_fetch_assoc($result)) {
            print_r($row);
        } else {
            echo 'No Client ID in DB';
        }
        mysqli_free_result($result);
    }
}
mysqli_stmt_close($stmt);