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 ?
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(*)')));
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.
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.
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');
