How to count number of rows within Zend Framework

Question

I need to count result rows of MySql query. here I extended TableGateway class to my class this is my code.

public function get_num_of_rows(){
    $sql = 'SELECT count(q_no) FROM questions';

    //code ????????????????
    $result = $this->select();

    return $result;
}

So how I execute SELECT count(q_no) FROM questions ?

Answer 1

There is no need to write own sql query. When you do $this->select(), you get an instance of Zend\Db\ResultSet\ResultSet. ResultSet has method count.

$result = $this->select();
return $result->count(); 

But do not forget to add 'options' => array('buffer_results' => true) to your DB adapter.

UPDATE:

It is the stupidest thing I've ever written somewhere. Always return from resource required only data. Here you need 1 scalar only. So https://stackoverflow.com/a/13810175/1353837 is correct.

Answer 2

For ZF-2, try this sample code:

 <?php
//var/www/html/zend_app/module/Api/src/Api/Model/ApiTable.php
namespace Api\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Expression;

class ApiTable extends AbstractTableGateway {



    public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Api());
        $this->initialize();
    }




    public function countTableData($from, $whereData = NULL) {
        $sql = new Sql($this->adapter);

        $select = $sql->select();
        $select->from($from);

        if($whereData)
        {
            $select->where($whereData);
        }
        $statement = $sql->prepareStatementForSqlObject($select);
        $results = $statement->execute();
        $resultSet = new ResultSet;
        $resultSet->initialize($results);
        $resultSet->buffer();
        return $resultSet->count();

    }


}

HTH.

Answer 3

If your table has huge number of records , You may get memory out error if you use

$result->count(); 

Instead use this , you can avoid the same

$select->from('TABLE_NAME')->columns(array('COUNT'=>new \Zend\Db\Sql\Expression('COUNT(*)')));

Answer 4

Or this:

$select->join('reports', 'user.user_id = reports.ruser_id', array('sdays' => new \Zend\Db\Sql\Expression('COUNT(rhours)')),$select::JOIN_INNER);

$select->group('ruser_id');