CODEWITHSUNDEEP
phpmysqlmysqli
Steven Oxley
Steven Oxley

Reputation: 6724

mysqli_stmt::num_rows() returns the wrong value

I was writing a database handler class in PHP using the mysqli class and prepared statements. I was attempting to print out the result. It didn't work right off the bat so I decided to do some debugging. I tried to use the num_rows() method from the mysqli_statement class, but it kept returning 0. I decided to write a small portion of the test code to keep it simpler so I could see what was going wrong. I was then able to return the data I wanted, but the num_rows() method still returns 0 even when it is actually selecting and retrieving some data. Here is the code:

$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if(mysqli_connect_errno())
{
  die('connection failed');
}

$statement = $mysqli->stmt_init();

$query = "SELECT name FROM table WHERE id = '2000'";
if($statement->prepare($query))
{
    $statement->execute();
    $statement->bind_result($name);
    $statement->fetch();
    $statement->store_result();
    echo $statement->num_rows();
    echo $name; 
}
else
{
    echo 'prepare statement failed';
    exit();
}

The expected result is:

1name

And the actual result is:

0name

Can anyone tell me why this is?

Upvotes: 1

Views: 2343

Answers (4)

Dharman
Dharman

Reputation: 33375

In order to be able to use mysqli_stmt::num_rows(), you need to fetch all rows into PHP. There are two ways to fetch everything: buffering using store_result() or manual fetching of all rows using fetch().

In your case, you have started manual fetching by calling fetch() once. You can't call store_result() when another fetch process is ongoing. The call to store_result() fails with an error*.

$statement->fetch();
$statement->store_result(); // produces error. See $mysqli->error;
echo $statement->num_rows();

The easiest solution is to swap the order in which you call these two methods.

$statement->store_result();
$statement->fetch(); // This will initiate fetching from PHP buffer instead of MySQL buffer
echo $statement->num_rows(); // This will tell you the total number of rows fetched to PHP

* Due to a bug in PHP, this error will not trigger an exception in the exception error reporting mode. The error message can only be seen with mysqli_error() function or its corresponding property.

Upvotes: 0

Scott
Scott

Reputation:

num_rows is not a method, it's a property.

Upvotes: 0

Nathan Strong
Nathan Strong

Reputation: 2400

I wonder if num_rows() is reporting relative to the current resultset. Try capturing num_rows() prior to fetching the data. e.g.

if($statement->prepare($query))
{
    $statement->execute();
    $statement->store_result();
    echo $statement->num_rows();
    $statement->bind_result($name);
    $statement->fetch();
    echo $name; 
}

Does that have any effect?

Upvotes: 6

Cetra
Cetra

Reputation: 2621

It doesn't look like you've declared $name.

Also, try removing bind_result() and fetch() so it reads something like this:

$statement->execute();

$statement->store_result();

printf("Number of rows: %d.\n", $statement->num_rows);

Upvotes: -1

Related Questions