Count on Zend_Db_Select

Question

Say I have a random zend_db_select object.

How can I perform a count on that object, so I know the amount of items that meet the query.

I tried the following:

$data->TotalRecords = $select->columns(new Zend_Db_Expr('COUNT(*)'))->query()->fetch();

But this gives me the following error:

Message: No table has been specifiedfor the FROM clause

The query by itself works fine and returns a resultset.

Answer 1

Use $select->__toString() method to output your generated query and see what is wrong with it.

If u dont have a from clause in your query add From() method to your select object.

Answer 2

If you use Zend_Db_Select, you have to call the from method to set the table name. With a Zend_Db_Table_Select, the table is passed in the constructor, so you don't need to call from.

Answer 3

There's a couple of ways of specifying the columns to fetch in a Zend_Db_Select. The following two product the same SQL

$select = $db->select()
             ->from('myTable', array())
             ->columns(array('TotalRecords' => new Zend_Db_Expr('COUNT(*)')));

$select = $db->select()
             ->from('myTable', array('TotalRecords' => new Zend_Db_Expr('COUNT(*)')));

The from method takes a first argument, the table name, and a second argument, an array of columns to fetch. If you're using an expression, you can specify a 'key' => Expr.

It's really easy to convert a Zend_Db_Select into a SQL string for debugging or use with other functions.

echo $select; // prints SELECT COUNT(*) AS `TotalRecords` FROM `myTable`

This uses a toString method, which is called automatically by Zend_Db fetch methods:

$total = $db->fetchOne($select); 

echo $total; //prints the number of rows matching the query

Where $db is an instance of Zend_Db.