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".

Related: Why does mysqli num_rows always return 0?

Dharman
– Dharman ♦

2023-04-29 23:23:41 +00:00
Commented Apr 29, 2023 at 23:23 — Dharman
– Dharman ♦, Commented Apr 29, 2023 at 23:23

Dharman · Accepted Answer · 2023-04-29 23:17:59Z

3

This code was taken from a comment in the PHP manual entry (now deleted for being irrelevant):

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

The problem here is MYSQLI_USE_RESULT. If you remove it, num_rows property will give you a correct number, because PHP will prefetch the entire result set and store it in the PHP process' memory - and so will be able to count the rows in it.

In case you need to use MYSQLI_USE_RESULT (to save memory), there is no way to get the number beforehand.

edited Apr 29, 2023 at 23:17

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jun 30, 2011 at 18:00

Phill Pafford

85.5k92 gold badges268 silver badges385 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

idragosalex · Accepted Answer · 2012-11-23 09:45:10Z

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)

answered Nov 23, 2012 at 9:45

idragosalex

1,6251 gold badge13 silver badges8 bronze badges

Comments

Alex · Accepted Answer · 2018-12-06 10:59:42Z

3

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;

answered Dec 6, 2018 at 10:59

Alex

899 bronze badges

Collectives™ on Stack Overflow

PHP MySQLi num_rows Always Returns 0

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related