This result is a forward only result set, calling

Question

In Zend app, I use Zend\Db\TableGateway and Zend\Db\Sql to retrieve data data from MySQL database as below.

Model -

public function getCandidateEduQualifications($id)
{
    $id  = (int) $id;

    $rowset = $this->tableGateway->select(function (Sql\Select $select) use ($id)
    {
        $select->where
            ->AND->NEST->equalTo('candidate_id', $id)
            ->AND->equalTo('qualification_category', 'Educational');
    });

    return $rowset;
}

View -

I just iterate $rowset and echo in view. But it gives error when try to echo two or more times. Single iteration works.

This result is a forward only result set, calling rewind() after moving forward is not supported

I can solve it by loading it to another array in view. But is it the best way ? Is there any other way to handle this ?

$records = array();
foreach ($edu_qualifications as $result) {
    $records[] = $result;
}

EDIT -

$resultSet->buffer(); solved the problem.

Answer 1

This worked for me.

public function fetchAll()
    {

        $select = $this->tableGateway->getSql()->select(); 
        $resultSet = $this->tableGateway->selectWith($select);
        $resultSet->buffer();
        $resultSet->next();

        return $resultSet;
    }

Answer 2

$sql = new Zend\Db\Sql($your_adapter);

$select = $sql->select('your_table_name'); 

$statement = $sql->prepareStatementForSqlObject($select);

$results = $statement->execute();

$resultSet = new ResultSet();

$resultSet->initialize($results);

$result = $resultSet->toArray();

Answer 3

Sure, It looks like when we use Mysql and want to iterate $resultSet, this error will happen, b/c Mysqli only does forward-moving result sets (Refer to this post: ZF2 DB Result position forwarded?)

I came across this problem too. But when add following line, it solved:

$resultSet->buffer();

but in this mentioned post, it suggest use following line. I just wonder why, and what's difference of them:

$resultSet->getDataSource()->buffer(); 

Answer 4

You receive this Exception because this is expected behavior. Zend uses PDO to obtain its Zend\Db\ResultSet\Resultset which is returned by Zend\Db\TableGateway\TableGateway. PDO result sets use a forward-only cursor by default, meaning you can only loop through the set once.

For more information about cursors check Wikipedia and this article.

As the Zend\Db\ResultSet\Resultset implements the PHP Iterator you can extract an array of the set using the Zend\Db\ResultSet\Resultset:toArray() method or using the iterator_to_array() function. Do be careful though about using this function on potentially large datasets! One of the best things about cursors is precisely that they avoid bringing in everything in one go, in case the data set is too large, so there are times when you won't want to put it all into an array at once.