Why is fetch_assoc returning false?

Question

$stmt = $mysqli->prepare("SELECT username, email, password, code FROM temp_users WHERE code = ?");
$stmt->bind_param('s', $code);
$stmt->execute();
$stmt->store_result();
//if SELECT statement returns 1, grab data.
if ($stmt->num_rows === 1) {
    echo "Got Row";

    $result = $stmt->get_result();
    var_dump($result);

    while ($row = $result->fetch_assoc()) {

        $username = $row['username'];
        $email = $row['email'];
        $password = $row['password'];
    }

This is really weird, the query must be going through because the script is echoing "Got Row", and I have no errors up to that point. But when I try to use $result->fetch_assoc() I get an error,and $result is spitting out false, so why is that? Please excuse how dumb this question may seem, I'm still learning how to use mysqli. :)

Answer 1

Your question is neither dumb nor weird. You are simply confused by store_result() and get_result().

Both of these functions fetch the whole record set from the database. Once the data is fetched you can't fetch it again. Therefore, you can't use both of these functions at the same time!

We can fix your code in two ways.

With store_result():

$stmt = $mysqli->prepare("SELECT username, email, password, code FROM temp_users WHERE code = ?");
$stmt->bind_param('s', $code);
$stmt->execute();
$stmt->store_result();
//if SELECT statement returns 1, grab data.
if ($stmt->num_rows === 1) {
    echo "Got Row";

    $stmt->bind_result($username, $email, $password);
    while ($stmt->fetch()) {
        // use the data here
        var_dump($username);
    }
}

with get_result():

$stmt = $mysqli->prepare("SELECT username, email, password, code FROM temp_users WHERE code = ?");
$stmt->bind_param('s', $code);
$stmt->execute();
$result = $stmt->get_result();
//if SELECT statement returns 1, grab data.
if ($result->num_rows === 1) { // <--- !!! We are using the result object here
    echo "Got Row";

    foreach ($result as $row) {
        $username = $row['username'];
        $email = $row['email'];
        $password = $row['password'];
    }
}