Zend Framework how to echo value of SUM query

Question

Hello,

I created a query for the zend framework, in which I try to retrieve the sum of a column, in this case the column named 'time'. This is the query I use:

$this->timequery = $this->db_tasks->fetchAll($this->db_tasks->select()->from('tasks', 'SUM(time)')->where('projectnumber =' . $this->value_project));


$this->view->sumtime = $this->timequery;

Echoing the query tells me this is right. But I can't echo the result properly. Currently I'm using:

echo $this->sumtime['SUM(time)'];

Returning the following error:

Catchable fatal error: Object of class Zend_Db_Table_Row could not be converted to string in C:\xampp\htdocs\BManagement\application\views\scripts\tasks\index.phtml  on line 46

Line 46 being the line with the echo in my view.

I've been searching now for two days on how to figure this out, or achieve the same result in a different way. Tried to serialize the value, but that didn't work either.

Is there somebody who knows how to achieve the total sum of a database column?

Any help is greatly appriciated!

note: Pretty new to zend framework...

Answer 1

The SQL you want is probably:

SELECT SUM(time) AS time_sum FROM tasks ...

Not sure how to do this in Zend. Then:

echo $this->sumtime['time_sum'];

Answer 2

Hi Rick,

Zend_Db has some nice utility methods like fetchAll (which you're using) to fetch different types of data:

• fetchAll - for a set of rows
• fetchRow - for a single row
• fetchOne - for a single cell

Most simply:

$sum = $db->fetchOne('SELECT SUM(time) FROM tasks WHERE project_number = ?', $this->value_project);

You can use Zend_Db_Select with these methods too, like in your question:

//note that the SUM(time) part is passed as a Zend_Db expression, as it is not a column
$select = $this->db_tasks->select()
                         ->from('tasks', new Zend_Db_Expr('SUM(time)'))
                         ->where('projectnumber =' . $this->value_project);

$this->timequery = $this->db_tasks->fetchOne($select);

This works because the Zend_Db_Select object implements a toString method, to produce SQL.