fetching data from various database tables

Question

I'm using zend framework 2.2 on xampp. I've a module 'Album'.

directory structure :

enter image description here

my database table:'album'

module code :

<?php

namespace Album;

use Album\Model\Album;
use Album\Model\AlbumTable;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

class Module {

    public function getAutoloaderConfig() {
        return array(
            'Zend\Loader\ClassMapAutoloader' => array(
                __DIR__ . '/autoload_classmap.php',
            ),
            'Zend\Loader\StandardAutoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__,
                ),
            ),
        );
    }

    public function getConfig() {
        return include __DIR__ . '/config/module.config.php';
    }

    public function getServiceConfig() {
        return array(
            'factories' => array(
                'Album\Model\AlbumTable' => function($sm) {
                    $tableGateway = $sm->get('AlbumTableGateway');
                    $table = new AlbumTable($tableGateway);
                    return $table;
                },
                'AlbumTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Album());
                    return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);
                },
            ),
        );
    }

}

controller code :

<?php

namespace Album\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Album\Model\Album;
use Album\Form\AlbumForm;

class AlbumController extends AbstractActionController {

    protected $albumTable;

    public function getAlbumTable() {
        if (!$this->albumTable) {
            $sm = $this->getServiceLocator();
            $this->albumTable = $sm->get('Album\Model\AlbumTable');
        }
        return $this->albumTable;
    }

    public function indexAction() {
        return new ViewModel(array(
                    'albums' => $this->getAlbumTable()->fetchAll(),
                    'active' => 'albumindex',
                ));
}
}

model code :

<?php

namespace Album\Model;

use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

 class AlbumTable
 {
     protected $tableGateway;

     public function __construct(TableGateway $tableGateway)
     {
       $this->tableGateway = $tableGateway;
     }

     public function fetchAll($paginated=false)
     {
           $resultSet =$this->tableGateway->select(function (Select $select) {
          $select->order('title ASC');
          });
            return $resultSet;
    }
}

view code :

 <?php
 // module/Album/view/album/album/index.phtml:

 $title = 'My albums';
 $this->headTitle($title);
 ?>
 <h1><?php echo $this->escapeHtml($title); ?></h1>
 <p>
     <a href="<?php echo $this->url('album', array('action'=>'add'));?>">Add new album</a>
 </p>

 <table class="table">
 <tr>
     <th>Title</th>
     <th>Artist</th>
     <th>&nbsp;</th>
 </tr>
 <?php foreach ($albums as $album) : ?>
 <tr>
     <td><?php echo $this->escapeHtml($album->title);?></td>
     <td><?php echo $this->escapeHtml($album->artist);?></td>
     <td>
         <a href="<?php echo $this->url('album',
             array('action'=>'edit', 'id' => $album->id));?>">Edit</a>
         <a href="<?php echo $this->url('album',
             array('action'=>'delete', 'id' => $album->id));?>">Delete</a>
     </td>
 </tr>
 <?php endforeach; ?>
 </table>

I get the output like the following picture. enter image description here

now I want to fetch data from different table('abm') while 'album' table still will be declared in module code.

  return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);

(and suppose my both data tables have same structure.so view page and controller will be same.) so what should I do for that ? how can I use TableGateway() in model code for different data table ?

-thanks.

Edit:

I did the following,

Module :

<?php

namespace Album;

use Album\Model\Album;
use Album\Model\AlbumTable;
**use Album\Model\AbmTable;**
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

class Module {

    public function getAutoloaderConfig() {
        return array(
            'Zend\Loader\ClassMapAutoloader' => array(
                __DIR__ . '/autoload_classmap.php',
            ),
            'Zend\Loader\StandardAutoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__,
                ),
            ),
        );
    }

    public function getConfig() {
        return include __DIR__ . '/config/module.config.php';
    }

    public function getServiceConfig() {
        return array(
            'factories' => array(
                'Album\Model\AlbumTable' => function($sm) {
                    $tableGateway = $sm->get('AlbumTableGateway');
                    $table = new AlbumTable($tableGateway);
                    return $table;
                },
                'AlbumTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Album());
                    return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);
                },
                **'Album\Model\AbmTable' => function($sm) {
                    $tableGateway = $sm->get('AbmTableGateway');
                    $table = new AbmTable($tableGateway);
                    return $table;
                },                        
                'AbmTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Album());
                    return new TableGateway('abm', $dbAdapter, null, $resultSetPrototype);
                },**                        
            ),
        );
    }

}

Controller :

<?php

namespace Album\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Album\Model\Album;
use Album\Form\AlbumForm;

class AlbumController extends AbstractActionController {

    protected $albumTable;protected $abmTable;

    public function getAlbumTable() {
        if (!$this->albumTable) {
            $sm = $this->getServiceLocator();
            $this->albumTable = $sm->get('Album\Model\AlbumTable');
        }
        return $this->albumTable;
    }

    **public function getAbmTable() {
        if (!$this->abmTable) {
            $sm = $this->getServiceLocator();
            $this->abmTable = $sm->get('Album\Model\AbmTable');
        }
        return $this->abmTable;
    }**    

    public function indexAction() {
        return new ViewModel(array(
                    'albums' => $this->getAlbumTable()->fetchAll(),
                    **'abms' => $this->getAbmTable()->fetchAll(),**
                ));
     }
}

Model (a new model page: 'AbmTable.php') :

<?php

namespace Album\Model;

use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

 class AbmTable
 {
     protected $tableGateway;

     public function __construct(TableGateway $tableGateway)
     {
       $this->tableGateway = $tableGateway;
     }

     public function fetchAll($paginated=false)
     {
           $result =$this->tableGateway->select(function (Select $select) {
          $select->order('title ASC');
          });
            return $result;
    }
 }

then I received '$abms' in view page with 'abm' table data.

Update :

it fetches different tables with same model.

Module :

namespace Album;

use Album\Model\AlbumTable;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

class Module {

    public function getAutoloaderConfig() {
        return array(
            'Zend\Loader\ClassMapAutoloader' => array(
                __DIR__ . '/autoload_classmap.php',
            ),
            'Zend\Loader\StandardAutoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__,
                ),
            ),
        );
    }

    public function getConfig() {
        return include __DIR__ . '/config/module.config.php';
    }

    public function getServiceConfig() {
        return array(
            'factories' => array(
// 'album' table-------------------------------------                 
                'Album\Model\AlbumTable\dbtable=album' => function($sm) {
                    $tableGateway = $sm->get('AlbumTableGateway');
                    $table = new AlbumTable($tableGateway);
                    return $table;
                },
                'AlbumTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Album());
                    return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);
                },
// 'abm' table-------------------------------------                        
                'Album\Model\AlbumTable\dbtable=abm' => function($sm) {
                    $tableGateway = $sm->get('AbmTableGateway');
                    $table = new AlbumTable($tableGateway);
                    return $table;
                },                        
                'AbmTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Album());
                    return new TableGateway('abm', $dbAdapter, null, $resultSetPrototype);
                },
            ),
        );
    }
}

Controller :

namespace Album\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;

class AlbumController extends AbstractActionController {

    protected $albumTable;
    protected $abmTable;

    public function getAlbumTable() {
        if (!$this->albumTable) {
            $sm = $this->getServiceLocator();
            $this->albumTable = $sm->get('Album\Model\AlbumTable\dbtable=album');
        }
        return $this->albumTable;
    }

    public function getAbmTable() {
        if (!$this->abmTable) {
            $sm = $this->getServiceLocator();
            $this->abmTable = $sm->get('Album\Model\AlbumTable\dbtable=abm');
        }
        return $this->abmTable;
    }    

    public function indexAction() {
        return new ViewModel(array(
                    'albums' => $this->getAlbumTable()->fetchAll(),
                    'abms' => $this->getAbmTable()->fetchAll(),
                ));
    }
 }

Model :

namespace Album\Model;

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

 class AlbumTable
 {
     protected $tableGateway;

     public function __construct(TableGateway $tableGateway)
     {
       $this->tableGateway = $tableGateway;
     }

     public function fetchAll($paginated=false)
     {
           $result =$this->tableGateway->select(function (Select $select) {
          $select->order('title ASC');
          });
            return $result;
    }
 }

kilop · Accepted Answer · 2013-11-18 13:03:25Z

1

If i understand you correctly you just have to make another TableGateway for another table 'abm' and make entity class for that then you can fetch both tables and show them in view.

answered Nov 18, 2013 at 13:03

kilop

3832 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1844626 Over a year ago

where should I make that ? in module or in model page ?

kilop Over a year ago

you have to do almost same thing what you did with Ablum in tutorial zend application but now with 'abm', then in your controller you should get your AbmTableGateway with fetchAll in it (for example) to get what you want from 'abm' table. Make that in module.php of your model, you can do another model with all that if you want and you think you need to separate 'abm' from 'album' logic.

user1844626 Over a year ago

please see my edit. New codes are in between ** sign. now its working. Is that exactly what you asked me to do ? But can I do it in same model page instead of creating new model page for new table ?

kilop Over a year ago

Well you can do it of course in the same model just inject another tablegateway via constructor. You will have then two tableGataways for bot your entities in AlbumTable, then do your stuff with them But i dont think its good practice.

Collectives™ on Stack Overflow

fetching data from various database tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related