$result->num_rows always returns 0

Question

after reading tons of threads, tutorials and whatever - I feel like posting here and hope that someone can help me. I tried out every advice I could get, but it's still not working.

Here is my code :

$prep_stmt = "SELECT id, DATE_FORMAT(added,'%d.%M'), title FROM offers ORDER BY added DESC LIMIT ?, ?;";
$stmt = $mysqli->prepare($prep_stmt);
$stmt->bind_param ('ii',$lowlimit, $page);
$stmt->execute();
$stmt->bind_result($id, $added, $title);
while ($stmt->fetch()) {
... # some random output here
}
$count = $stmt->num_rows;
echo "c -> ". $count;exit;

I always get "c -> 0" ... but there IS output already ... so what am I doing wrong ? :/

Answer 1

You need to call the store_result() method before accessing the num_rows property.

From a comment on PHP manual documentation:

If you do not use mysqli_stmt_store_result(), and immediately call this function after executing a prepared statement, this function will usually return 0 as it has no way to know how many rows are in the result set as the result set is not saved in memory yet.

mysqli_stmt_store_result() saves the result set in memory thus you can immedietly use this function after you both execute the statement AND save the result set.

Your code should look like:

$stmt->execute();
$stmt->store_result();

$stmt->bind_result($id, $added, $title);

while ($stmt->fetch()) {
    # some random output here...
}

$count = $stmt->num_rows;