Database operations [Data Management and Document Sharing]

In the previous chapter we learned how to implement a basic database operation, namely, table insert. In this section, you will learn all the basic database operations necessary for building a simple CRUD (Create, Read, Update and Delete) interface.

More on TableGateway

The TableGateway class extends AbstractTableGateway, which implements TableGatewayInterface. The interface definition of TableGatewayInterface is provided in the following code snippet; all the basic table operations are defined

in the interface:

interface Zend\Db\TableGateway\TableGatewayInterface

{

public function getTable();

public function select($where = null); public function insert($set);

public function update($set, $where = null); public function delete($where);

}

The TableGateway class offers a wide range of methods to perform basic database operations; some of the most frequently used methods are explained in the following section:

◆        getTable(): Returns a string which contains the table name mapped with the

TableGateway object. For example, see the following code:

$myTableName = $myTableGateway->getTable();

◆        select($where = null): Used to select a set of rows with the criteria specified

in $where; it can either be a where condition based on Zend\Db\Sql\Where or an

array of criteria. For example, see the following code:

$rowset = $myTableGateway->select( array('id' => 2));

◆        insert($set): Used to insert the data defined in $set into the table as a new record. For example, see the following code:

$myTableGateway->insert( array('id' => 2, 'name'=>'Ravi'));

◆        update($set, $where = null): Used to update a set of rows with the criteria specified in $where; it can either be a where condition based on Zend\Db\Sql\ Where or an array of criteria. $set holds the data that will be updated for all the records matched with $where. For example, see the following code:

$rowset = $myTableGateway->update(array('name' => 'Jerry') , array('id' => 2));

◆        delete($where): Used to delete a set of rows with the criteria specified in

$where; it can either be a where condition based on Zend\Db\Sql\Where

or an array of criteria. For example, see the following code:

$myTableGateway->delete( array('id' => 2));

◆        getLastInsertValue(): Returns the last insert value for the table's primary key. the return type is an integer. For example, see the following code:

$myTableGateway->insert( array('name'=>'Ravi'));

$insertId = $myTableGateway-> getLastInsertValue ();

In this task we will be creating an administration user interface for managing users in our application. The following operations will include listing all users, editing existing users, deleting users, and adding users:

1.       Modify CommunicationApp/module/Users/src/Users/Model/UserTable.

php using the following code. Add the following functions:

‰        fetchAll()

‰        getUser($id)

‰        getUserByEmail($userEmail)

‰        deleteUser($id)

public function fetchAll()

{

$resultSet = $this->tableGateway->select(); return $resultSet;

}

public function getUser($id)

{

$id  = (int) $id;

$rowset = $this->tableGateway->select(array('id' => $id));

$row = $rowset->current(); if (!$row) {

throw new \Exception("Could not find row $id");

}

return $row;

}

public function getUserByEmail($userEmail)

{

$rowset = $this->tableGateway->select(array('email' =>

$userEmail));

$row = $rowset->current(); if (!$row) {

throw new \Exception("Could not find row $ userEmail");

}

return $row;

}

public function deleteUser($id)

{

$this->tableGateway->delete(array('id' => $id));

}

2.       Create a new controller for user management under CommunicationApp/ module/Users/src/Users/Controller/UserManagerController.php.

3.       The UserManagerController controller will have the following actions:

‰        indexAction(): This is used to render all available users in the system, and we will also render links to add/edit and delete links as shown in the following code:

$userTable = $this->getServiceLocator()

->get('UserTable');

$viewModel  = new ViewModel(array(

'users' => $userTable->fetchAll())); return $viewModel;

‰        editAction(): This action is used to render the edit form to modify the information related to the user:

$userTable = $this->getServiceLocator()

->get('UserTable');

$user = $userTable->getUser(

$this->params()->fromRoute('id'));

$form = $this->getServiceLocator()

->get('UserEditForm');

$form->bind($user);

$viewModel  = new ViewModel(array( 'form' => $form,

'user_id' => $this->params()->fromRoute('id')

)); return $viewModel;

4.     Create the necessary views and modify the module's config/module.config.

php file to specify a unique child route to access this controller:

'user-manager' => array( 'type'      => 'Segment', 'options' => array(

'route'    => '/user-manager[/:action[/:id]]', 'constraints' => array(

'action'     => '[a-zA-Z][a-zA-Z0-9_-]*', 'id'     => '[a-zA-Z0-9_-]*',

),

'defaults' => array(

'controller' => 'Users\Controller\UserManager', 'action'  => 'index',

),

),

),

5.       Finally add the new controller to the invokables array: 'Users\Controller\UserManager' => 'Users\Controller\ UserManagerController',

6.       Now open your web browser and access the controller, log in to your application, and open http://comm.-app.local/users/user-manager. You should be able to see a page similar to the one given in thefollowing screenshot:

The Edit user link should redirect you to an user edit form like the one in the following screenshot:

The Delete user link can be used to remove the user from the user list:

What just happened?

We have now created an administration user interface for adding, modifying, and removing users from our communication application. We have utilized all the core functionalities of the TableGateway model and created functions for performing CRUD operations on the table access objects.

Going forward, we will be making use of some of the more advanced applications

of TableGateway.

 Before we move on to the next section, here is a small task for you to practice. Your task for this section will be to create a new Add User form. Refer to the following screenshot:

This form will be similar to the Register Form that we created in the previous chapter. Once the form is submitted, the user will be taken back to the user listing page. A link to this form will have to be added in the user listingpage.