Issues

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

Issue Type: Bug Created: 2012-04-28T05:15:43.000+0000 Last Updated: 2012-06-04T17:09:23.000+0000 Status: Resolved Fix version(s): Reporter: Pascal Rota (erasertwo) Assignee: Ralph Schindler (ralph) Tags: - Zend\Db

Related issues: - ZF2-262

Attachments:

Description

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

<pre class="highlight">
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.

Comments

Posted by Duccio Gasparri (dgasparri) on 2012-05-12T01:56:18.000+0000

This appears to be a clone of [ZF2-262] http://framework.zend.com/issues/browse/ZF2-262

Proposed git pull https://github.com/zendframework/zf2/pull/1201 (commit https://github.com/dgasparri/zf2-testserver/… )

Posted by Adam Lundrigan (adamlundrigan) on 2012-05-12T02:24:48.000+0000

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

Posted by Pascal Rota (erasertwo) on 2012-05-12T04:34:24.000+0000

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

Posted by Duccio Gasparri (dgasparri) on 2012-05-13T01:25:11.000+0000

@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

Posted by Adam Lundrigan (adamlundrigan) on 2012-05-13T11:17:27.000+0000

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

Posted by Ralph Schindler (ralph) on 2012-06-04T17:09:23.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts