Welcome to Abdul Malik Ikhsan's Blog

Zend Framework 2 ‘Cheat Sheet’ : Zend\Db

Posted in Tutorial PHP, Zend Framework 2 by samsonasik on January 15, 2013

zf2-zendframework2Zend 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

Advertisements

37 Responses

Subscribe to comments with RSS.

  1. Jose Manuel Orts said, on January 29, 2013 at 8:25 pm

    Hi Abdul,

    I wrote this code with the full framework and I have the same problem that before through another ways… I confused…. All ways I tried, all ways it failed with the same error:

    Catchable fatal error: Object of class stdClass could not be converted to string in D:\xampp\htdocs\haystack\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php on line 258

    This is my code:

    adapter = $adapter;
    // $this->resultSetPrototype = new HydratingResultSet();
    // $this->initialize();
    // }

    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($comment, $id)
    {
    $data = array(
    'autor' => $comment->getAutor(),
    'email' => $comment->getEmail(),
    'comment' => $comment->getComment(),
    'ip' => $comment->getIp(),
    'browser' => $comment->getBrowser(),
    'fecha' => $comment->getIdPublication()
    );
    $this->update($data, array('id' => $id));
    }

    //inserting...
    public function insertdataUser($comment)
    {
    $data = array(
    'autor' => $comment->getAutor(),
    'email' => $comment->getEmail(),
    'comment' => $comment->getComment(),
    'ip' => $comment->getIp(),
    'browser' => $comment->getBrowser(),
    'fecha' => $comment->getIdPublication()
    );
    $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();
    }
    }

    In Module class


    'initializers' => array(
    function ($instance, $sm)
    {
    if ($instance instanceof \Zend\Db\Adapter\AdapterAwareInterface)
    {
    $instance->setDbAdapter($sm->get('Zend\Db\Adapter\Adapter'));
    }
    }
    ),
    'invokables' => array(
    'Frontend\Model\CommentTableGateway ' => 'Frontend\Model\CommentTableGateway'
    )

    Controller

    $table = $sm->get('Frontend\Model\CommentTableGateway');
    $table->insertdataUser($commentObj);//here fired the error

    But it’s very curious, because I tried of three diferents ways and ever got the error, I missed? obviously…

    The other ways are:

    _sql = $sql;
    }

    public function fetchAll()
    {
    $select = $this->_sql->select(‘publications’);
    $select->order(‘fecha DESC’);
    // $select->limit(5);
    $statement = $this->_sql->prepareStatementForSqlObject($select);
    $results = $statement->execute();
    $resultSet = new ResultSet();
    $rows = $resultSet->initialize($results);

    $theComments = array();
    foreach ($rows as $index => $cmt) {
    $comment = new Comment();
    $comment->setId($cmt->id);
    $comment->setIdPublication($cmt->id_publication);
    $comment->setAutor($cmt->autor);
    $comment->setEmail($cmt->email);
    $comment->setComment($cmt->comment);
    $comment->setFecha($cmt->fecha);
    $comment->setIp($cmt->ip);
    $comment->setBrowser($cmt->browser);

    $theComments[$index] = $cmt;
    $cmt = null;
    }

    return $theComments;
    }

    public function saveComment($comment)
    {
    $data = array(
    ‘autor’ => $comment->getAutor(),
    ’email’ => $comment->getEmail(),
    ‘comment’ => $comment->getComment(),
    ‘ip’ => $comment->getIp(),
    ‘browser’ => $comment->getBrowser(),
    ‘fecha’ => $comment->getIdPublication()
    );
    $insert = $this->_sql->insert(‘comments’);
    $insert->values($data);
    $statment = $this->_sql->prepareStatementForSqlObject($insert);
    $result = $statment->execute();
    return $result;
    }

    }

    Module class
    //as a factory
    ‘Frontend\Model\DaoComment’ => function($sm) {
    return new Model\DaoComment(new Sql($sm->get(‘Zend\Db\Adapter\Adapter’)));
    },

    Controller

    $sm = $this->getServiceLocator();
    $this->_commentTable = $sm->get(‘Frontend\Model\DaoComment’);
    $this->getCommentTable()->saveComment($commentObj);//here fired

    And the last way

    _hydratator = new \Zend\Stdlib\Hydrator\ClassMethods;
    $rowObjectPrototype = new Comment();
    $this->_resultSet = new \Zend\Db\ResultSet\HydratingResultSet($this->_hydratator, $rowObjectPrototype);
    $this->_commentTableGateway = new TableGateway(‘comments’, $adapter, null, $this->_resultSet );
    }
    public function fetchAll()
    {
    return $this->_commentTableGateway->select();
    }

    public function saveComment(Comment $comment)
    {
    // $data = array(
    // ‘autor’ => $comment->getAutor(),
    // ’email’ => $comment->getEmail(),
    // ‘comment’ => $comment->getComment(),
    // ‘ip’ => $comment->getIp(),
    // ‘browser’ => $comment->getBrowser(),
    // ‘fecha’ => $comment->getIdPublication()
    // );

    $id = (int)$comment->getId();
    if ($id == 0) {
    $this->_commentTableGateway->insert($this->_hydratator->extract($comment));
    } else {
    if ($this->getComment($id)) {
    $this->_commentTableGateway->update($data, array(‘id’ => $id));
    } else {
    throw new \Exception(‘El comentario que queire editar no exite’);
    }
    }
    }

    public function getComment($id)
    {
    $id = (int) $id;
    $rowset = $this->_commentTableGateway->select(array(‘id’ => $id));
    $row = $rowset->current();
    if (!$row) {
    throw new \Exception(“Could not find row $id”);
    }
    return $row;
    }
    }

    Module class ‘as a factory for sm’
    ‘Comment\Model\CommentTable’ => function($sm) {
    // $tableGateway = $sm->get(‘CommentTableGateway’);
    $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
    $table = new CommentTable($dbAdapter);
    return $table;
    },

    What I’m doing wrong?

    Any help is appreciated!

    Kind regards

    • samsonasik said, on January 30, 2013 at 12:51 am

      what the value of $comment->getAutor(); ? :), I think you should echo-ing all params you pass before passing to Tablegateway.

      • Jose Manuel Orts said, on January 30, 2013 at 2:02 am

        That getter has got a String value, this is the dump of the object which comes to the insert method

        object(Frontend\Model\Comment)#206 (8) { [“_id”:protected]=> NULL [“_idPublication”:protected]=> int(3) [“_autor”:protected]=> string(6) “Andres” [“_email”:protected]=> string(16) “andres@gmail.com” [“_comment”:protected]=> string(16) “andres@gmail.com” [“_ip”:protected]=> string(3) “::1” [“_browser”:protected]=> object(stdClass)#207 (30) { [“browser_name_regex”]=> string(6) ” \^.*$\” [“browser_name_pattern”]=> string(1) “*” [“browser”]=> string(15) “Default Browser” [“version”]=> string(1) “0” [“majorver”]=> string(1) “0” [“minorver”]=> string(1) “0” [“platform”]=> string(7) “unknown” [“alpha”]=> string(0) “” [“beta”]=> string(0) “” [“win16”]=> string(0) “” [“win32”]=> string(0) “” [“win64”]=> string(0) “” [“frames”]=> string(1) “1” [“iframes”]=> string(0) “” [“tables”]=> string(1) “1” [“cookies”]=> string(0) “” [“backgroundsounds”]=> string(0) “” [“cdf”]=> string(0) “” [“vbscript”]=> string(0) “” [“javaapplets”]=> string(0) “” [“javascript”]=> string(0) “” [“activexcontrols”]=> string(0) “” [“isbanned”]=> string(0) “” [“ismobiledevice”]=> string(0) “” [“issyndicationreader”]=> string(0) “” [“crawler”]=> string(0) “” [“cssversion”]=> string(1) “0” [“supportscss”]=> string(0) “” [“aol”]=> string(0) “” [“aolversion”]=> string(1) “0” } [“_fecha”:protected]=> NULL }

        How you can see the variables has got value. And this is the dump of the $data

        array(6) { [“autor”]=> string(6) “Andres” [“email”]=> string(16) “andres@gmail.com” [“comment”]=> string(16) “andres@gmail.com” [“ip”]=> string(3) “::1” [“browser”]=> object(stdClass)#207 (30) { [“browser_name_regex”]=> string(6) “\^.*$\” [“browser_name_pattern”]=> string(1) “*” [“browser”]=> string(15) “Default Browser” [“version”]=> string(1) “0” [“majorver”]=> string(1) “0” [“minorver”]=> string(1) “0” [“platform”]=> string(7) “unknown” [“alpha”]=> string(0) “” [“beta”]=> string(0) “” [“win16”]=> string(0) “” [“win32”]=> string(0) “” [“win64”]=> string(0) “” [“frames”]=> string(1) “1” [“iframes”]=> string(0) “” [“tables”]=> string(1) “1” [“cookies”]=> string(0) “” [“backgroundsounds”]=> string(0) “” [“cdf”]=> string(0) “” [“vbscript”]=> string(0) “” [“javaapplets”]=> string(0) “” [“javascript”]=> string(0) “” [“activexcontrols”]=> string(0) “” [“isbanned”]=> string(0) “” [“ismobiledevice”]=> string(0) “” [“issyndicationreader”]=> string(0) “” [“crawler”]=> string(0) “” [“cssversion”]=> string(1) “0” [“supportscss”]=> string(0) “” [“aol”]=> string(0) “” [“aolversion”]=> string(1) “0” } [“fecha”]=> int(3) }

        And finally when I remove the dump() and die() I get again:

        Catchable fatal error: Object of class stdClass could not be converted to string in D:\xampp\htdocs\haystack\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php on line 258

        I confused, this I’ve already done, but I don’t find what’s happening.

        This is my Module class(It’s got some test in the factories):

        getApplication()->getEventManager();
        $eventManager->attach(‘dispatch’, array($this, ‘loadAuthorData’ ));
        $eventManager->attach(‘dispatch’, array($this, ‘loadCategories’ ));
        $eventManager->attach(‘dispatch’, array($this, ‘loadEnlaces’ ));
        }

        public function loadAuthorData(MvcEvent $evt)
        {
        $controller = $evt->getTarget();
        $sm = $evt->getApplication()->getServiceManager();
        $author = $sm->get(‘Author\Model\AuthorTable’);
        $controller->layout()->author = $author->fetchAll();
        }

        public function loadCategories(MvcEvent $evt)
        {
        $controller = $evt->getTarget();
        $sm = $evt->getApplication()->getServiceManager();
        $ctgs = $sm->get(‘Categoria\Model\CategoriaTable’);
        $controller->layout()->categories = $ctgs->fetchAll();
        }
        public function loadEnlaces(MvcEvent $evt)
        {
        $controller = $evt->getTarget();
        $sm = $evt->getApplication()->getServiceManager();
        $enlaces = $sm->get(‘Enlace\Model\EnlaceTable’);
        $controller->layout()->enlaces = $enlaces->fetchAll();
        }

        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(
        ‘Frontend\Model\DaoPublication’ => function($sm) {
        return new DaoPublication(new Sql($sm->get(‘Zend\Db\Adapter\Adapter’)));
        },
        ‘Author\Model\AuthorTable’ => function($sm) {
        $tableGateway = $sm->get(‘AuthorTableGateway’);
        $table = new AuthorTable($tableGateway);
        return $table;
        },
        ‘AuthorTableGateway’ => function ($sm) {
        $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
        $hydrator = new \Zend\Stdlib\Hydrator\ClassMethods;
        $rowObjectPrototype = new Model\Author;
        $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $rowObjectPrototype);
        return new TableGateway(‘author’, $dbAdapter, null, $resultSet);
        },
        ‘Categoria\Model\CategoriaTable’ => function($sm) {
        $tableGateway = $sm->get(‘CategoriaTableGateway’);
        $table = new CategoriaTable($tableGateway);
        return $table;
        },
        ‘CategoriaTableGateway’ => function ($sm) {
        $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
        $hydrator = new \Zend\Stdlib\Hydrator\ClassMethods;
        $rowObjectPrototype = new Model\Categoria;
        $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $rowObjectPrototype);
        return new TableGateway(‘categorias’, $dbAdapter, null, $resultSet);
        },
        ‘Enlace\Model\EnlaceTable’ => function($sm) {
        $tableGateway = $sm->get(‘EnlaceTableGateway’);
        $table = new EnlaceTable($tableGateway);
        return $table;
        },
        ‘EnlaceTableGateway’ => function ($sm) {
        $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
        $hydrator = new \Zend\Stdlib\Hydrator\ClassMethods;
        $rowObjectPrototype = new Model\Enlace;
        $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $rowObjectPrototype);
        return new TableGateway(‘links’, $dbAdapter, null, $resultSet);
        },
        ‘Comment\Model\CommentTable’ => function($sm) {
        // $tableGateway = $sm->get(‘CommentTableGateway’);
        $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
        $table = new CommentTable($dbAdapter);
        return $table;
        },
        ‘CommentTableGateway’ => function ($sm) {
        $dbAdapter = $sm->get(‘Zend\Db\Adapter\Adapter’);
        $hydrator = new \Zend\Stdlib\Hydrator\ClassMethods;
        $rowObjectPrototype = new Model\Comment;
        $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $rowObjectPrototype);
        return new TableGateway(‘comments’, $dbAdapter, null, $resultSet);
        },
        ‘Frontend\Model\DaoComment’ => function($sm) {
        return new Model\DaoComment(new Sql($sm->get(‘Zend\Db\Adapter\Adapter’)));
        },

        ) ,
        ‘initializers’ => array(
        function ($instance, $sm)
        {
        if ($instance instanceof \Zend\Db\Adapter\AdapterAwareInterface)
        {
        $instance->setDbAdapter($sm->get(‘Zend\Db\Adapter\Adapter’));
        }
        }
        ),
        ‘invokables’ => array(
        ‘Frontend\Model\CommentTableGateway ‘ => ‘Frontend\Model\CommentTableGateway’
        ),);
        }
        }

        Thanks a lot.

      • samsonasik said, on January 30, 2013 at 2:10 am

        try change

        var_dump($var)
        

        to

        echo '<pre>';
        print_r($var) , 
        

        It can make your debugging more readable 🙂

      • Jose Manuel Orts said, on January 30, 2013 at 2:20 am

        You have reason! I note… 🙂

        Really thanks, I was focusing on the wrong problem and had a variable I added at the last minute and poof there is the problem.

        See you Abdul!

  2. Jose Manuel Orts said, on January 30, 2013 at 2:14 am

    Sorry my imprudence!! The confusion has got me…. for a moment… You are rigth.
    I was passing an object actually.

    Thanks a lot Abdul and sorry my confusion, it is a blow to my… 🙂

    Kind regards!!

  3. […] "Cheat sheet" für ZendDb […]

  4. Manish Kutaula said, on February 8, 2013 at 5:47 pm

    hey abdul
    $resultSet = $this->select(function (Sql\Select $select) use ($keyword) {

    in this code if it have to use two variables in the query, how do we write it. The following throws error
    $resultSet = $this->select(function (Sql\Select $select) use ($keyword1,$keyword2) {

    how can i use two or more php variables with select object

  5. Ziyan-Junaideen said, on February 11, 2013 at 1:54 am

    Reblogged this on Ziyan Junaideen and commented:
    Better than Zend Manuel! This shows some good examples in clear format! Got me out of living hell!

  6. Ziyan-Junaideen said, on February 19, 2013 at 1:17 am

    Dude, cool stuff. I am needing to inner join two tables. Some thing like SELECT * FROM foo, bar WHERE foo.this_id = bar.that_id;

    I would prefer to do it using the full framework through the skeleton applicaiton.

  7. Ziyan-Junaideen said, on February 19, 2013 at 1:18 am

    Accidental click…
    How can I achieve this? I am completely a newbe to ZendFramework.

    • samsonasik said, on February 19, 2013 at 1:25 am

      please read my post carefully and not hurry, you will get the answer.

      • Ziyan-Junaideen said, on February 19, 2013 at 1:53 am

        You are right, I am tensed up. I need to take a break and then come read your post. Thanks!

  8. Tom Shaw said, on May 9, 2013 at 1:46 am

    Awesome work great blog! Thanks a million for the awesome articles.

  9. Harvey said, on July 30, 2013 at 6:02 pm

    Another filtering command you didn’t include (that I guessed up today) is the $select->where->or; e.g.

    $select->where->equalTo(‘role_name’, ‘User1’);
    $select->where->or;
    $select->where->equalTo(‘role_name’, ‘User2’);

  10. Harvey said, on July 30, 2013 at 6:02 pm

    Great post btw! Really helpful!

  11. Name said, on August 18, 2013 at 12:31 am

    Hi.
    I’m trying to use the Zend\Db component with oracle oci8 adapater.
    I want to get the lastGeneratedValue but this method just returns null.
    I posted an issue on github and now it is deleted. I don’t know what went wrong.

    How can I do it right, so that somebody my implement it.
    Unfortunetly I don’t have enought time right now.

  12. zqd said, on August 19, 2013 at 3:13 pm

    I am using doctrine 2 instead of Zend\Db\Adapter. I attempted to replace the adapter but I dont think i am doing something correct. What changes do I need to make so as use Doctrine 2 as opposed to Zend\Db\Adapter?

  13. George said, on September 26, 2013 at 1:33 am

    Again, great job! My problem is that the setDbAdapter() from AdapterAwareInterface gets triggered only when I call the MyModelTable from controller by using something like $sm = $this->getServiceLocator();
    $myObject = $sm->get(‘MyModelTable ‘); . How to proceed when I want to call MyModelTable from another model. I dont want to keep the business logic in Controller or MyModelTable, I need another model that does that. I haven’t tried the CommonModelTableAbstractFactory tip yet, I’ll give it a go. Many thanks.

  14. doli said, on February 14, 2014 at 2:54 pm

    Hi, can you please suggest me to find the solution.
    I have query like this:

    SELECT tbl_leads . * , tbl_lead_category.vLeadCategoryName AS vLeadCategoryName, tbl_lead_category.vLeadCategoryIcon AS vLeadCategoryIcon, Concat( vFirst, ‘ ‘, vLast ) AS vFirst
    FROM tbl_leads
    LEFT JOIN tbl_lead_category ON tbl_lead_category.iLeadCategoryID = tbl_leads.iLeadCategoryID
    LEFT JOIN tbl_user ON tbl_user.iUserID = tbl_leads.iUserID
    WHERE (
    tbl_leads.eDeleted = ‘0’
    )
    AND (
    tbl_leads.iUserID = ‘1’
    )
    AND (
    vCompanyName LIKE ‘%t%’
    OR vCompanyUrl LIKE ‘%t%’
    OR vPersonName LIKE ‘%t%’
    OR vDesignationName LIKE ‘%t%’
    OR vSkypeID LIKE ‘%t%’
    OR vEmailID LIKE ‘%t%’
    OR vPhoneNumber LIKE ‘%t%’)

    I need a parentheses in where clause, which i can’t got
    I have written my code like

    $select = $this->tableGateway->getSql()->select();
    $select->columns(array(‘*’));
    $select->join(‘tbl_lead_category’, ‘tbl_lead_category.iLeadCategoryID = tbl_leads.iLeadCategoryID’, array(‘vLeadCategoryName’, ‘vLeadCategoryIcon’), “LEFT”)
    ->join(‘tbl_user’, ‘tbl_user.iUserID = tbl_leads.iUserID’,array(“vFirst”=>new \Zend\Db\Sql\Predicate\Expression(“Concat(vFirst,’ ‘,vLast)”)),”LEFT”);
    //$select->join(‘tbl_designation’, ‘tbl_designation.iDesignationID = tbl_leads.iDesignationID’, array(‘vDesignationName’), “LEFT”);

    $where = new \Zend\Db\Sql\Where();
    $where->NEST->equalTo(‘tbl_leads.eDeleted’,’0′)->UNNEST;

    if(isset($iUserID) && $iUserID != “”)
    $where->AND->NEST->equalTo (‘tbl_leads.iUserID’, $iUserID)->UNNEST;

    // start for searching
    if (isset($data[‘sSearch’]) && $data[‘sSearch’] != “”) {
    for ($i = 0; $i OR->like($aColumns[$i], “%” . $data[‘sSearch’] . “%”);
    // how can i get parentheses to my where which i am searching
    }
    }

    /*i have tried this, but its giving error: not nested**/
    if (isset($data[‘sSearch’]) && $data[‘sSearch’] != “”) {
    $where->AND->NEST;
    for ($i = 0; $i OR->like($aColumns[$i], “%” . $data[‘sSearch’] . “%”);
    // how can i get parentheses to my where which i am searching
    }
    $where->UNNEST;
    }

  15. Dona said, on March 27, 2014 at 10:40 pm

    Hi! Very good job!

    I’m a very strange problem! My zend run in xampp. All work fine but..when i insert datas , i have a double entry in my DB!
    example:
    name:mark
    email:mark@mark.com

    in my db i find:

    id name email
    0 mark mark@mark.com
    1 mark mark@mark.com

    I’m sure is not a mysql server problem why i tried in insert from a simple php script and all work fine. I’m sure that the values passed from the insert are not duplicated. Why it happen?
    The simple $tablegateway->insert($data) write the sam row two times in the db.
    I hope you help me, i don’t find nothing about this.

  16. Namjith Aravind said, on January 30, 2015 at 5:52 pm

    In some situations we dont need all entity object properties of entity model object we set in Resultset setArrayObjectPrototype, in that situations do we need to create another entity model class for that?
    Or there is any filtration ?

    For example
    ‘UserTableGateway’ => function ($sm) {
    $dbAdapter = $sm->get(‘MainDbAdapter’);
    $resultSetPrototype = new ResultSet();
    $resultSetPrototype->setArrayObjectPrototype(new User());
    return new TableGateway(‘system_login_manager’, $dbAdapter, null, $resultSetPrototype);
    }

    in this case User entity model class have more than 10 properties, but i only need to return user_email and user_type properties of User entity type.

    • samsonasik said, on January 31, 2015 at 8:13 am

      if use HydratingResultSet, you don’t need to define all of them if you want.
      but modelling is important for your maintainable application 😉

  17. Janley Santos said, on October 22, 2015 at 5:26 am

    How do I create a test in this case?
    Using where- > in…
    public function selectByCode($code)
    {
    $sqlSelect = $this->tableGateway->getSql()
    ->select()->columns(array(‘cficodctg’, ‘cfidesctg’))
    ->where->in(‘cficodctg’, $code);
    return array(‘resultSet’ => $this->tableGateway->select($sqlSelect));
    }

  18. Sarel said, on April 5, 2016 at 5:15 am

    Hi,

    Im interested in using the Tablegateway as an alternative to PDO inside the SLIM 3 framework. My goal is to create a new custom users class and define all the CRUD rules etc inside it What is the best and shortest way to accomplish this. I require your guidance in this, any suggestions would be appreciated

    require_once ‘../vendor/autoload.php’;

    use Ti\Helpers\Usrclass;
    use Zend\Db\TableGateway\TableGateway;

    $container[‘adapter’] = function ($c) {
    return new Zend\Db\Adapter\Adapter(array(
    ‘driver’ => ‘pdo_mysql’,
    ‘database’ => ‘dest’,
    ‘username’ => ‘root’,
    ‘password’ => ‘xxxxx’
    ));
    };

    $container[‘usersinfo_model’] = function ($c) {
    return new TableGateway(‘usersinfo’, $c[‘adapter’]);
    };

    $container[‘Usrclass’] = function ($c) {
    return new Usrclass($c[‘usersinfo_model’]); <<tableGateway = $tableGateway;
    }

    public function fetchAll() {
    $resultSet = $this->tableGateway->select();
    return $resultSet;
    }

    public function getoneuser($userid) {
    $rowset = $this->tableGateway->select(array(‘userid’ => $userid));
    return $rowset;
    }
    }

    routes.php
    ————–
    $app->get(‘/’, function ($request, $response, $args) {

    $rowset = $this->Usrclass->fetchAll(); <<<usersinfo_model->select(); <<<< this line of code does work but its not using the usrclass
    echo '

    ';
        print_r(
                $rowset->toArray()
        );
        echo '

    ‘;

    • samsonasik said, on April 5, 2016 at 10:23 am

      I’m sorry, I am not familiar with slim.

      • Sarel said, on April 5, 2016 at 12:55 pm

        Hi, i understand you don’t know slim, die sample code is mainly plain php with a di php container. For a standalone program what would your aproch be and can you please comment on my code

      • samsonasik said, on April 5, 2016 at 2:05 pm

        Then You JUST missed some steps, please do effort! `toArray()` only works with HydratingResultSet, so you should can do:

        use Zend\Db\ResultSet\HydratingResultSet;
        
        new TableGateway('usersinfo', $c['adapter'], null,new HydratingResultSet());
        

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: