mysqli prepared statement num_rows returns 0 while query returns greater than 0 [duplicate]

I have a simple prepared statement for an email that actually exists:

$mysqli = new mysqli("localhost", "root", "", "test");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = 'SELECT `email` FROM `users` WHERE `email` = ?';
$email = '[email protected]';

if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param('s', $email);
    $stmt->execute();

    if ($stmt->num_rows) {
        echo 'hello';
    }

    echo 'No user';
}

Result: echos No user when it should echo hello

I ran the same query in the console and got a result using same email as above.

I tested using a simple mysqli query as well:

if ($result = $mysqli->query("SELECT email FROM users WHERE email = '[email protected]'")) {
    echo 'hello';

}

Result: what I expected hello

Also $result's num_rows is 1.

Why is the prepared statment's num_row not greater than 0?

Solution 1:

When you execute a statement through mysqli, the results are not actually in PHP until you fetch them -- the results are held by the DB engine. So the mysqli_stmt object has no way to know how many results there are immediately after execution.

Modify your code like so:

$stmt->execute();
$stmt->store_result(); // pull results into PHP memory

// now you can check $stmt->num_rows;

See the manual

This doesn't apply to your particular example, but if your result set is large, $stmt->store_result() will consume a lot of memory. In this case, if all you care about is figuring out whether at least one result was returned, don't store results; instead, just check whether the result metadata is not null:

$stmt->execute();
$hasResult = $stmt->result_metadata ? true : false;

See the manual

Solution 2:

call function $stmt->store_result() after $stmt->execute() this link might help http://php.net/manual/en/mysqli-stmt.num-rows.php

Solution 3:

I think it's missing

$stmt->store_result();

if ($stmt->num_rows) {
    echo 'hello';
}

http://php.net/manual/en/mysqli-stmt.num-rows.php