PHP MySQLi num_rows Always Returns 0

Question

I have built a class which leverages the abilities of PHP's built-in MySQLi class, and it is intended to simplify database interaction. However, using an OOP approach, I am having a difficult time with the num_rows instance variable returning the correct number of rows after a query is run. Take a look at a snapshot of my class...

class Database {
//Connect to the database, all goes well ...

//Run a basic query on the database
  public function query($query) {
  //Run a query on the database an make sure is executed successfully
    try {
    //$this->connection->query uses MySQLi's built-in query method, not this one
      if ($result = $this->connection->query($query, MYSQLI_USE_RESULT)) {
        return $result;
      } else {
        $error = debug_backtrace();

        throw new Exception(/* A long error message is thrown here */);
      }
    } catch (Exception $e) {
      $this->connection->close();

      die($e->getMessage());
    }
  }

//More methods, nothing of interest ...
}

Here is a sample usage:

$db = new Database();
$result = $db->query("SELECT * FROM `pages`"); //Contains at least one entry
echo $result->num_rows; //Returns "0"
exit;

How come this is not accurate? Other values from result object are accurate, such as "field_count". Any help is greatly appreciated.

Thank you for your time.

Solution 1

Possible Bug: http://www.php.net/manual/en/mysqli-result.num-rows.php#104630

Code is from source above (Johan Abildskov):

$sql = "valid select statement that yields results"; 
if($result = mysqli-connection->query($sql, MYSQLI_USE_RESULT)) 
{ 
          echo $result->num_rows; //zero 
          while($row = $result->fetch_row()) 
        { 
          echo $result->num_rows; //incrementing by one each time 
        } 
          echo $result->num_rows; // Finally the total count 
}

Could also validate with the Procedural style:

/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);

Solution 2

I had the same problem and found the solution was to put:

$result->store_result();

..after the execution of the $query and before

echo $result->num_rows;

Solution 3

This could be normal behavior when you disable buffering of the result rows with MYSQLI_USE_RESULT

Disabling the buffer means that it`s up to you to fetch, store and COUNT the rows. You should use the default flag

$this->connection->query($query, MYSQLI_STORE_RESULT); 

Equivalent of

$this->connection->query($query)

Comments

[Oliver Spryn]

Gahh... It was so easy. Thanks for pointing that out. Both the PHP site's example and my example queries were using the "MYSQLI_USE_RESULT" constant. I removed that and it works like a charm! Thanks for your help, Phil!

[Kevin Cittadini]

Just a note for use of MYSQLI_USE_RESULT: PHP mysql::query If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()