Setup model and table classes to fetch data from MySQL database using TableGateway in Zend Framework2

February 17, 2013
By

In this article, we will see how to fetch data from a mysql database and display it in Zend Framework2. For this we need to set up model class, table class, TableGateway class and service manager.

This article belongs the series “Web application development with Zend Framework 2“.

In this article we will continue with the application developed in the article titled “Different Layouts for different Controller Actions in Zend Framework2“.


1. Setup the application

Please follow the article “Different Layouts for different Controller Actions in Zend Framework2” to setup the application and ensure that a web page as shown in the given Figure1 is obtained.

Screenshot of the application where data fetched from an array variable

Figure 1 : Screenshot of the application where data is fetched from an array variable


2. Setup the database

  • Create a database namely zf2studentapp in MySql
  • Create a table namely student in the database zf2studentapp

create table if not exists student(
    id      int     auto_increment primary key,
    name    varchar(100),
    department varchar(50),
    marks   double
);

  • Insert few rows into the table student

insert into student(name,department,marks) values('A Mitra','Physics',77);
insert into student(name,department,marks) values('Mukherjee P S','Physics',89);
insert into student(name,department,marks) values('Rani Mathew','Computer Science',91);
insert into student(name,department,marks) values('Rakesh Krishna','Computer Science',72);
insert into student(name,department,marks) values('Faisal Ahmed','Computer Science',93);

3. Create a model class in the file Zf2StudentApp/module/Student/src/Student/Model/Student.php


<?php
namespace Student\Model;

class Student{
    public $id;
    public $name;
    public $department;
    public $marks;

    public function exchangeArray($data){
        $this->id = (isset($data['id']))?$data['id']:null;
        $this->name = (isset($data['name']))?$data['name']:null;
        $this->department = (isset($data['department']))?$data['department']:null;
        $this->marks = (isset($data['marks']))?$data['marks']:null;
    }
}


4. Create a table class in the file Zf2StudentApp/module/Student/src/Student/Model/StudentTable.php


<?php
namespace Student\Model;

use Zend\Db\TableGateway\TableGateway;

// A table class for the database table student
class StudentTable{

    // This table class does database operations using $tableGateway
    protected $tableGateway;

    // Service manager injects TableGateway object into this class
    public function __construct(TableGateway $tableGateway){
        $this->tableGateway = $tableGateway;
    }

    // Fetching all the student table rows
    public function fetchAll(){
        $resultSet = $this->tableGateway->select();
        return $resultSet;
    }
}


5. Instantiating StudentTable class using Service manager in the file Zf2StudentApp/module/Student/Module.php


<?php
namespace Student;

use Student\Model\Student;
use Student\Model\StudentTable;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\ResultSet\ResultSet;

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

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

    // Automatically invoked by service manager
    public function getServiceConfig(){
        return array(
            'factories' => array(
                // Instantiating StudentTable class by injecting TableGateway
                'Student\Model\StudentTable'=>function($sm){
                    $tableGateway = $sm->get('StudentTableGateway');
                    $table = new StudentTable($tableGateway);
                    return $table;
                },
                //Instantiating TableGateway to inject to StudentTable class
                'StudentTableGateway'=>function($sm){
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Student());
                    return new TableGateway('student', $dbAdapter,null,$resultSetPrototype);
                }
            )
        );
    }
}


6. Entering database connection details in the file Zf2StudentApp/config/autoload/global.php


<?php

return array(
    // Database connection details
    'db'=>array(
        'driver'=>'Pdo',
        'dsn'=>'mysql:dbname=zf2studentapp;host=localhost',
        'driver_options'=>array(
            PDO::MYSQL_ATTR_INIT_COMMAND =>'SET NAMES \'UTF8\''
        )
    ),

    'service_manager'=>array(
        'factories'=>array(
            'Zend\Db\Adapter\Adapter'=>'Zend\Db\Adapter\AdapterServiceFactory'
        )
    )
);


7. Entering database credential details in the file Zf2StudentApp/config/autoload/local.php


<?php

return array(
    // Database credentials
    'db'=>array(
        'username'=>'root',
        'password'=>'mysql'
    )
);


8. Fetching data in StudentController (Zf2StudentApp/module/Student/src/Student/Controller/StudentController.php)


<?php
namespace Student\Controller;
use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;

class StudentController extends AbstractActionController{
    protected $studentTable;

    public function indexAction(){
        // Setting layout for this action
        $this->layout("layout/student_layout");

        return new ViewModel(array(
            // Fetching data from database
            'students'=>$this->getStudentTable()->fetchAll()
        ));
    }

    // Getting StudentTable object to do database operations
    public function getStudentTable(){
        if(!$this->studentTable){
            $sm = $this->getServiceLocator();
            $this->studentTable = $sm->get("Student\Model\StudentTable");
            return $this->studentTable;
        }
    }
}


9. Accessing Student object in Zf2StudentApp/module/Student/view/student/student/index.phtml


<h1>
    Students List
</h1>

<table border="1">
    <thead>
        <tr>
            <th>No</th>
            <th>Name</th>
            <th>Department</th>
            <th>Marks</th>
        </tr>
    </thead>
    <tbody>

<?php

$i=1;
foreach($students as $student){
    echo "<tr>";
    echo "<td>" . $i++ . "</td>";
    echo "<td>" . $student->name . "</td>";
    echo "<td>" . $student->department . "</td>";
    echo "<td>" . $student->marks . "</td>";
    echo "</tr>";
}
?>

    </tbody>
</table>



10. Screenshot of the application in execution

Displaying data fetched from MySql Database using TableGateway in Zend Framework2

Figure 2 : Displaying data fetched from MySql Database using TableGateway in Zend Framework2


11. Download Application Source

The source for this application can be downloaded from here.

The application repository is available here


How to hire me?

I am George Mathew, working as software architect and Android app developer at wptrafficanalyzer.in

You can hire me on hourly basis or on project basis for Android applications development.

For hiring me, please mail your requirements to info@wptrafficanalyzer.in.

My other blogs
store4js.blogspot.com


Android Knowledge Quiz

Ready to test your knowledge in Android? Take this quiz :



Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Be friend at g+

Subscribe for Lastest Updates

FBFPowered by ®Google Feedburner