Zend_Db_Table_Abstract count

Question

On Zend_Db_Table_Abstract I'm using this code to get the count of results for my select:

$this->setTableName('visitors');
$select = $this->select()
               ->from('visitors')
               ->columns(array('total' => new Zend_Db_Expr('COUNT(*)')))
               ->where('...');
$visits = $this->_fetch($select);

Is there a better way, ie, just to return the count. This returns some other data in an array... I just want the count of results. In straight mySQl the equivalent would be select count(mycol) from visitors where ....

only count. `$count = (int)$this->getAdapter()->fetchOne($select);` try :) — SMka, Sep 12 '11 at 16:54
`fetchOne()` Fetches the first column of the first row of the SQL result. — SMka, Sep 12 '11 at 16:56

Bill Karwin · Accepted Answer · 2011-09-12T17:04:29.097

4

This is untested, but should get you started:

$results = $this->getAdapter()
  ->query("SELECT COUNT(*) AS total FROM visitors WHERE ...")
  ->fetchAll();
$visits = $results[0]["total"];

You aren't required to use the Table/Select interface for every query.

update: +1 to the comment from @SMka, for pointing out that this can be even simpler:

$visits = $this->getAdapter()
  ->fetchOne("SELECT COUNT(*) AS total FROM visitors WHERE ...");

edited Sep 12 '11 at 17:04

answered Sep 12 '11 at 16:43

Bill Karwin

538,548
86
673
828

Your last sentence is a relief! – Owen Sep 12 '11 at 16:50
1

I worked on Zend_Db_Select, and I tried to make it clear in the docs that the interface is to help when you need to build up a query piece by piece from application logic or variables. It's not necessary to use that interface when you you can simply spell out the full SQL query in a string. *SQL is already a domain-specific language.* – Bill Karwin Sep 12 '11 at 17:01
@Bill Karwin but parsing SQL takes time wouldn't Zend_Db_Select with PDO extension gona save recourse ?? – Mr Coder Sep 12 '11 at 17:31
@jason bourne: Zend_Db_Select simply helps to put together a SQL query string. The query string is still sent to the RDBMS server, where it is parsed just like if you wrote it yourself. There is no savings. – Bill Karwin Sep 12 '11 at 17:35

Mr Coder · Answer 2 · 2011-10-23T05:04:48.347

4

$count = $this->select()->from($this,'COUNT(*)')->query()->fetchColumn();

edited Oct 23 '11 at 05:04

answered Sep 12 '11 at 16:46

Mr Coder

8,169
5
45
74

This looks interesting, do I add something like count( mycol) in the query() bit? – Owen Sep 12 '11 at 16:54
no nothing needed , you can replace $this with 'visitors' if you like too. – Mr Coder Sep 12 '11 at 17:19
OK, but how do I add a WHERE condition, is it query('WHERE ...')? – Owen Sep 12 '11 at 17:36

score 1 · Answer 3 · answered Sep 12 '11 at 16:44

1

http://framework.zend.com/manual/en/zend.db.adapter.html

see fetchOne

$this->setTableName('visitors');
$select = $this->select()
               ->from('visitors')
               ->columns(array('total' => new Zend_Db_Expr('COUNT(*)')))
               ->where('...');
$count = $this->getAdapter()->fetchOne($select);

answered Sep 12 '11 at 16:44

SMka

3,021
18
14

Does that fetch all the data and then give the count, or just fetch the count? – Owen Sep 12 '11 at 16:52

score 0 · Answer 4 · answered Jun 02 '12 at 00:13

0

here ya go

public function fetchCount($data)
{
    $sql = "SELECT COUNT(*) AS count FROM table WHERE field = ".$data;

    $count = $this->db->fetchOne($sql, array('count'));

    return $count;
}

answered Jun 02 '12 at 00:13

Patrioticcow

26,422
75
217
337

Zend_Db_Table_Abstract count

4 Answers4