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);

Answer 2

Not sure if it goes in the right direction, but if you already limiting the result to first row, than you basicly only need to check if the result is empty. If you would be using:

$result = mysql_query($query);    

i would have suggested going with something like:

if (!$result)

But what you do doesn't look like usual php calls, still hope this helps.