Zend

Question

I am trying to make a group using the Zend framework. Here is my code:

$table = new TableClass(); $select = $table->select(); $select->from ("table", array("date", "column1" => "sum(column1)")); $select->group ( array ("date") ); $results = $table->fetchAll ($select); $result = $results[0]; $date = $result->date; $column1 = $result->column1; 

TableClass extends 'Zend_Db_Table_Abstract'.

I can view the query by looking at the mysql query log. The query is well-formed - column1 is specified in the query, and the results look correct if I run the query in mysql workbench.

I cannot access data in 'column1' - I always get this exception:

Throw exception "Zend_Db_Table_Row_Exception" with the message "The specified column" column1 "is not in the row"

However, I can access the date column without problems.

I tried:

• access to columns by array index: $ Result [0] but you get an exception (cannot access columns by index).

• do not use a column alias: $ select-> from ("table", array ("date", "sum (column1)")); $ column1 = $ result ["sum (column1)"]; but you get an exception (there is no such column as "sum (column1)").

• throwing at Zend_Db_Expr: "column1" => new Zend_Db_Expr ("sum (column1)") but this does not help.

Some other examples I've seen suggest using column names without aggregate functions, i.e. "column1" instead of "sum (column1)", but this does not seem to me the answer - the query does not have any aggregate functions, so mysql does not know what to do with it.

Any help was appreciated.

Answer 1

First, a quick tip for working with Zend_Db_Select (and for the extension Zend_Db_Table_Select), you can view the generated SQL by calling the toString method. It is very important to make sure that your code generates the correct query before working with the result set:

 $select = $table->select(); $select->from ("table", array("date", "column1" => "sum(column1)")); $select->group ( array ("date") ); $sql = (string) $select; //Retrieve SQL as a string 

Or simply

 die($select); //print SQL 

I wrote the following test script using your example and have no problems:

 class Table extends Zend_Db_Table_Abstract { protected $_primary = 'id'; protected $_name = 'table'; } $db = Zend_Db::factory('Pdo_Mysql', array( 'dbname' => 'test', 'username' => 'root', 'password' => '', 'host' => 'localhost' )); $table = new Table($db); $select = $table->select(); $select->from ($table, array("date", "column1" => new Zend_Db_Expr("sum(column1)"))); $select->group ( array ("date") ); $sql = (string) $select; echo $sql; $results = $table->fetchAll ($select); $result = $results[0]; $date = $result->date; $column1 = $result->column1; echo '<br>' . $date . ': ' . $column1; 

Use Zend_Debug :: dump ($ result); to check data inside Zend_Db_Table_Row, if necessary.

In my case, the generated SQL is as follows:

 SELECT `table`.`date`, sum(column1) AS `column1` FROM `table` GROUP BY `date`