ZF2-281: Cannot Count rows in a MySQLi Result Set


I have an issue with the TableGateway, ResultSet classes. If I call after a select the count() method, it will always return 0 (An example is shown below). The first row can be accessed by current()-stmt without a problem. Furthermore it is not possible to loop twice over the same result set with rewind(), current() and next(). Even going through the resultSet after a call to count() is not possible.   Here is a short code snipped to demonstrate the error (short):

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Adapter\Adapter;
$adapter = new Adapter(array(
                    'driver'   => 'Mysqli',
                    'database' => ,
                    'username' => ,
                    'password' => 
$tab     = new TableGateway(, $adapter);
$resultSet = $tab->select();
echo $resultSet->count();  // will always return 0

  I belive that the error is in this case in Zend\Db\Adapter\Driver\Mysqli\Result class. The result ressource is a mysqli_stmt class, but no mysqli_stmt::store_result() is called and therefore the result set is never transfered from the query and can only be accessed once.   A possible fix can be found in Pull Request #1080 https://github.com/zendframework/zf2/pull/1080.  


Not just MySQLi, but also PDO (tested w/ SQLite). Reproduce script: https://gist.github.com/2659662

I tested the proposed git pull https://github.com/zendframework/zf2/pull/1201 and it would fix this problem (at least with the MySQLi backend).

@Adam Lundrigan, could you clarify? do you mean that also PDO has this problem, or that the proposed pull solves also the PDO one? Because PDO calls are handled by a different class (Pdo\Result instead of Mysqli\Result), and although the issues are probably the same as the mysqli version, I did not touch the PDO. I did only minor changes on the common class ResultSet\ResultSet, how did that affect the overall behavior of Pdo?

We're writing a Unit Test for the Mysqli, it would be nice to add one for the Pdo too. If you want to take a look (was mixed with another pull, look from the middle of the discussion) https://github.com/zendframework/zf2/pull/1201

@Duccio: Sorry, I should have been clearer. I've been using Zend\Db with the PDO adapter to connect to a SQLite database, and I am encountering the same issue as explained in this ticket (row count always zero). I haven't looked in the code to see if the problem with both adapters comes from the same code or if they are separate issues, so instead of opening a new ticket yet I just commented on this one.

This is fixed in beta4. For mysqli, by default it will produce unbuffered result sets. In order to buffer these results, a buffer() method was added to the ResultInterface.

Call this before calling count(), and it shall work. There is also a setting in the Mysqli Statement object to always produce buffered result sets for mysqli. Since the default PHP behavior is to produce unbuffered sets, this is also the default in the Mysqli driver.