Zend Framework 2 ‘Cheat Sheet’ : Zend\Db

Zend Framework is a Full stack and component based framework. It can be used by standalone without full of components. At this time, I will post about Zend\Db. I hope this post will help You to try this component with or without full framework.

A. Installation
A.1 Without Full Framework
A.1.a. configure composer.json

{
    "repositories": [
        {
            "type": "composer",
            "url": "http://packages.zendframework.com/"
        }
    ],
    "require": {
        "php": ">=5.3.3",
        "zendframework/zend-db": "2.*"
    }
}

A.1.b. type command :

composer install

A.2 With Full Framework
You can create app with full framework via ZendSkeletonApplication, it can simplify your steps.

COMPOSER_PROCESS_TIMEOUT=50000 composer create-project --repository-url="http://packages.zendframework.com" zendframework/skeleton-application path/to/install

B. Using Zend\Db
B.1 Without ‘Full’ Framework
B.1.a. configure autoload

include './vendor/autoload.php';

B.1.b. Setting Db Adapter

use Zend\Db\Adapter\Adapter;

$adapter = new Adapter(array(
    'driver'   => 'pdo_mysql',
    'database' => 'zf2',
    'username' => 'root',
    'password' => ''
));

B.1.c. Using TableGateway
The Table Gateway object is intended to provide an object that represents a table in a database, and the methods of this object mirror the most common operations on a database table.
If you want data can be displayed as array for simplify debugging, you must add dependency : zendframework/zend-stdlib

{
    "repositories": [
        {
            "type": "composer",
            "url": "http://packages.zendframework.com/"
        }
    ], 
    "require": {
        "php": ">=5.3.3",
        "zendframework/zend-db": "2.*",
        "zendframework/zend-stdlib": "2.*"
    }
}

and run

composer update

For example, you have table named sample_table :

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

//using TableGateway instance
$sampleTable = new TableGateway('sample_table', $adapter, null,new HydratingResultSet());
//selecting...
$rowset = $sampleTable->select();
echo '<pre>';
print_r(
    $rowset->toArray()  
);
echo '</pre>';

//updating... $data, $where
$sampleTable->update(array(
    'name' => 'iman'
), array('id'=>1));

//inserting...
$sampleTable->insert(array(
    'name' => 'Dony'
));

By using HydratingResultSet, you can ‘convert’ your iterator data into array.
d. Using RowGateway
Row Data Gateway object is intended to provide an object that represents a single database row. RowGateway is a sub-component of Zend\Db that implements the Row Gateway pattern. RowGateway is greatly if used in conjunction with the TableGateway feature.

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\TableGateway\Feature\RowGatewayFeature;

$table = new TableGateway('sample_table', $adapter, new RowGatewayFeature('id'));
$results = $table->select(array('name' => 'joni'));

$artistRow = $results->current();
//updating...
if ($artistRow) {
    $artistRow->name = 'Adam';
    $artistRow->save();
}
//deleting...
$artistRow = $table->select(array('name' => 'Adam'))->current();
if ($artistRow) {
    $artistRow->delete();
}

B.2 With ‘Full’ Framework
B.2.a Configure config/autoload/global.php & config/autoload/local.php
B.2.a.1 global.php

return array(
    'db' => array(
        'driver'   => 'pdo_mysql',
        'database' => 'zf2',
        'driver_options' => array(
             PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
             //if you're using mysqli, 
             //you can set driver options such as
             // MYSQLI_INIT_COMMAND => 'SET AUTOCOMMIT = 1',
        ),
        'options' => array(
            //if you're using mysqli driver
            //you should set buffer_results => true 
            //'buffer_results' => true
        ),
    ),
    'service_manager' => array(
        'factories' => array(
            'Zend\Db\Adapter\Adapter'
                    => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

B.2.a.2 local.php ( You should put your database credentials in config/autoload/local.php so that they are not in the git repository (as local.php is ignored) )

return array(
    'db' => array(
        'username' => 'root',
        'password' => '',
    ),
);

B.2.b Create a class extends AbstractTableGateway implements AdapterAwareInterface

namespace SanAuth\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Db\Adapter\AdapterAwareInterface;

class UserTable extends AbstractTableGateway
    implements AdapterAwareInterface
{
    protected $table = 'zf2_users'; //tablename

    public function setDbAdapter(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new HydratingResultSet();
        
        $this->initialize();
    }
    
    // selecting...
    public function fetchAll()
    {
        $resultset = $this->select();
        return $resultset->toArray();
    }
    
    //updating... 
    //format $data passed : array('cols' => 'colsvalue');
    public function updateUser($data, $id)
    {
         $this->update($data, array('id' => $id));
    }

    //inserting...
    public function insertdataUser($data)
    {
         $this->insert($data);
         
         //you can return lastId for Your App need
         return $this->getLastInsertValue(); 
    }
   
    //deleting...
    public function deleteUser($id)
    {
        $rowset = $this->select(array('id' => $id));
        $row = $rowset->current();
        
        $row->delete();
    }
}

If you work with schema, You can set it with TableIdentifier.

$this->table = new \Zend\Db\Sql\TableIdentifier('zf2_users', 'yourschema');

B.2.c Register into Service Manager

public function getServiceConfig()
{
    return array(
        'initializers' => array(
           function ($instance, $sm) {
	       //use this only once for reduce repetitive injection 
               //in registering class in SM	
               if ($instance instanceof \Zend\Db\Adapter\AdapterAwareInterface) {
	           $instance->setDbAdapter($sm->get('Zend\Db\Adapter\Adapter'));
		}
            }
	),
        'invokables' => array(
            'SanAuth\Model\UserTable' =>  'SanAuth\Model\UserTable'
        ),
    );
}

Configuration done! and you can get it and run!
C. Addendum
C.1 Filtering

use Zend\Db\Sql;
....
function getData($keyword)
{
    $resultSet = $this->select(function (Sql\Select $select) use ($keyword) {
      //only columns...
      $select->columns(array('cols1','cols2'));
       //join
       $select->join('othertable', "othertable.id = zf2_users.id", 
                 array('othertable.columns1','othertable.columns2'), 'left'); //left is type of join
       //like... 
       $select->where->like('user_name', "%$keyword%");
       //equalTo
       $select->where->equalTo('role_name', 'User');
       //notequalTo
       $select->where->notequalTo('role_id', 2);
       //between
       $select->where->between('id', 2, 5); //identifier,min,max
  
       //NEST
       $select->where
                    ->AND->NEST->like('firstname', "%$keyword%")
                    ->OR->like('lastname', "%$keyword%");
       
      //if you will work with
      //'native' expression, use Sql\Expression
      $select->where->notequalTo('name', new Sql\Expression('all(select name from othertableagain)'));     
      //or use predicate...
      $select->where
            ->addPredicate(new Sql\Predicate\Expression('LOWER(user_name) = ?',
                                strtolower($name)));
       
       //group
       $select->group(array('role_id')); 
       // order 
       $select->order(array('id asc'));
   
    });

    return $resultSet;
}

C.2 Tip :
You should create abstract factory if you want it can be called by Service Manager whenever you forgot to register it.

namespace SanCommons\Service;

use Zend\ServiceManager\AbstractFactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;

class CommonModelTableAbstractFactory implements AbstractFactoryInterface
{
    public function canCreateServiceWithName(ServiceLocatorInterface $locator, $name, $requestedName)
    {
        return (substr($requestedName, -5) === 'Table');
    }

    public function createServiceWithName(ServiceLocatorInterface $locator, $name, $requestedName)
    {
        $db = $locator->get('Zend\Db\Adapter\Adapter');
        $tablemodel = new $requestedName;
        $tablemodel->setDbAdapter($db);
        
        return $tablemodel;
    }
}

Then register this Abstract Factory into Service Manager :

public function getServiceConfig()
{
    return array(
        'abstract_factories' => array(
            'SanCommons\Service\CommonModelTableAbstractFactory',
        ),
    );
}    

Thats it! Hope this helpful 😉

References :
1. http://en.wikipedia.org/wiki/Row_data_gateway
2. http://zf2.readthedocs.org/en/latest/modules/zend.db.table-gateway.html
3. http://zf2.readthedocs.org/en/latest/modules/zend.db.table-gateway.html
4. http://stackoverflow.com/questions/14190872/how-to-add-sql-expression-in-front-of-column-name-in-zf2
5. https://github.com/ralphschindler/Zend_Db-Examples
6. http://zf2.readthedocs.org/en/latest/user-guide/database-and-models.html