InnerJoin in Zend Framework 2

Question

The problem is i get only 1 image from the database. What i want is to get all the images into a different array so i can view them per project.

IndexController.php

public function projectsAction() {

  $projects = $this->getProjectsTable()->fetchAll();

  return new ViewModel(array(
  'projects' => $projects,
  ));
 }

public function getProjectsTable() {
 if (!$this->projectsTable) {
     $sm = $this->getServiceLocator();
     $this->projectsTable = $sm->get('Application\Model\ProjectsTable');
    }
  return $this->projectsTable;
}

ProjectsTable.php

public function fetchAll() {
 $select = new Select();

  $select->from('projects', array('projects.*'))
      ->join('project_images', 'projects.id=project_images.project_id', array('*'))
      ->where('projects.id=project_images.project_id');

  //echo $select->getSqlString();
  $resultSet = $this->selectWith($select);
  $resultSet->buffer();
  return $resultSet;
 }

OUTPUT:

 array(17) {

**PROJECTS STARTS FROM HERE**

      ["id"]=>
      string(1) "1"
      ["name"]=>
      string(55) "Name"
      ["country"]=>
      string(6) "country"
      ["location_country"]=>
      string(13) "location"

**PROJECT IMAGES STARTS FROM HERE**

    ["project_id"]=>
      string(1) "1"
      ["image_name"]=>
      string(29) "1_1370202251.808419328090.png"
      ["date"]=>
      string(22) "2013-06-02 07:44:11 PM"
    }

Answer 1

I sloved it by doing this:

In Controller

public function projectsAction() {

  $projects = $this->getProjectsTable()->fetchAll();

  $i = 0;
  foreach ($projects as $project) {
   $imgs = array();
   $pros[] = $project;
   $images = $this->getProjectImagesTable()->fetchAll($project->id);
   foreach ($images as $img) {
    $imgs[] = $img;
    $pros[$i]->images = $imgs;
   }
   $i++;
  }

  return new ViewModel(array(
      'projects' => $pros
  ));
 }

In Projects

<?php
//IN MODEL FOLDER
namespace Application\Model;

class Projects 
{
  public function exchangeArray($data)
  {
    $this->id     = (isset($data['id'])) ? $data['id'] : null;
    $this->name = (isset($data['name'])) ? $data['name'] : null;
    $this->country = (isset($data['country'])) ? $data['country'] : null;

   }
}

?>

In ProjectsTable

<?php
 //IN MODEL FOLDER
namespace Application\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;

class ProjectsTable extends AbstractTableGateway {

 protected $table = 'projects';

 public function __construct(Adapter $adapter) {
  $this->adapter = $adapter;
  $this->resultSetPrototype = new ResultSet();
  $this->resultSetPrototype->setArrayObjectPrototype(new Projects());
  $this->initialize();
 }

 public function fetchAll() {
  $select = new Select();

  $select->from('projects', array('projects.*'));

  //echo $select->getSqlString();
  $resultSet = $this->selectWith($select);
  $resultSet->buffer();
  return $resultSet;
 }

}

In ProjectImages

<?php
//IN MODEL FOLDER

namespace Application\Model;

class ProjectImages {

 public $id, $project_id, $image_name, $date;

 public function exchangeArray($data) {
  $this->id = (isset($data['id'])) ? $data['id'] : null;
  $this->project_id = (isset($data['project_id'])) ? $data['project_id'] : null;
  $this->image_path = (isset($data['image_path'])) ? $data['image_path'] : null;
  $this->date = (isset($data['date'])) ? $data['date'] : null;
 }

}

In ProjectImagesTable

<?php

namespace Application\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;

class ProjectImagesTable extends AbstractTableGateway {

 protected $table = 'project_images';

 public function __construct(Adapter $adapter) {
  $this->adapter = $adapter;
  $this->resultSetPrototype = new ResultSet();
  $this->resultSetPrototype->setArrayObjectPrototype(new ProjectImages());
  $this->initialize();
 }

 public function fetchAll($id) {
  $select = new Select();

  $select->from('project_images', array())
          ->where(array('project_id' => $id));

  //echo $select->getSqlString();
  $resultSet = $this->selectWith($select);
  $resultSet->buffer();
  return $resultSet;
 }

}