Fetching data from mysql database in the VIEW structure of Zend 2.2?

Question

I am a newbie in php+Zend programming, so need your valuable advice. 1. I have a table in mysql (phpmyadmin), the attributes in the table are ~ user_id, expense_id, date month, year, expense. 2. I have .phtml file (index.phtml) in the View folder (Zend 2.2). It is accessed by indexAction() in the Controller page. Code:

return viewmodel ( return array=>( 'years'=>$this->getExpenseTable()->fetchAll($user_id); )),

[sorry if it's not in the proper format]. This function is meant to return all the values from the db, when I put it into a table with foreach. The code in index.phtml is below:

escapeHtml($expense->expense);?> .....and so on......

Now my problem is: a) I cannot use the variable 'years' in another table with another foreach loop in the same index.phtml file. it says, "this is a forward-only result-set." I tried implementing unset() and rewind(), both did not work. b) I want to take unique value of the attribute 'year' from the table (as table header you might think) and put the summation of expenses under each year.

Jurian Sluiman Jurian Sluiman · Accepted Answer · 2013-07-13T13:39:38

You have multiple questions which should perhaps be wrapped in multiple questions, but anyhow, here it goes:

Iterate multiple times over a single resultset:

Some drivers allow buffering. The value of $years is a Zend ResultSet object. You can call $years->buffer() before you loop to enable the internal buffer so you can iterate twice:

// $set is ResultSet
$set->buffer();

foreach ($set as $result) {} // first time
foreach ($set as $result) {} // second time

Extract the year:

You can use simple view logic for that:

// example resultset
$result = array(
  array('year' => '2012', 'value' => 'foo'),
  array('year' => '2012', 'value' => 'bar'),
  array('year' => '2013', 'value' => 'baz'),
);

// store year for check
$year = null;
foreach ($result as $item) {
    if ($item['year']) !== $year) {
        $year = $item['year'];
        echo $year;
    }

    echo $item['value'];
}

Fetching data from mysql database in the VIEW structure of Zend 2.2?

1 Answers