num_rows method not bringing back number of selected rows

Question

This is the code:

$q = $this->db->prepare("SELECT id FROM `users` WHERE username=? AND password=? LIMIT 1");
$q->bind_param('ss', $username, $password);
$q->execute();
printf('Rows returned: %i', $q->num_rows); 

I am using MySQLi to try and check a users login credentials. Everything works and the query gets executed and data is returned (I have checked this seperately) but I only get this output: Rows returned:

Is there anything wrong here? I'm new to using MySQLi but going by the PHP.net examples there's nothing wrong with this code. Cheers.

Answer 1

From the docs:

If you do not use mysqli_stmt_store_result( ), and immediatley 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.

So you need to change it to:

$q->bind_param('ss', $username, $password);
$q->execute();
$q->store_result();
printf('Rows returned: %d', $q->num_rows);