Monday 18 January 2016

Database Query in Zend framework 2

Database Query in Zend framework 2

Question: How to set database connection in Config file?
Create local.php file in config/autoload/ and following code .
return array(
'db' => array(
    'driver'         => 'Pdo',
    'dsn'            => 'mysql:dbname=mydb;host=localhost',
    'username'       =>'',
    'password'      =>'',
    'driver_options' => array(
'service_manager' => array(
    'aliases' => array(
'db' => 'Zend\Db\Adapter\Adapter',

In controller,you can get database object
$dbObj = $this->getServiceLocator()->get('db');

Question: How to connect mysql in ZF2?
$adapter = new Zend\Db\Adapter\Adapter(array(
    'driver' => 'Mysqli',
    'database' => 'mydb',
    'username' => 'root',
    'password' => ''

Question: What are different database driver provided by ZF2 ?
  1. Pdo_Mysql: MySQL through the PDO extension
  2. Pdo_Sqlite: SQLite though the PDO extension
  3. Pdo_Pgsql: PostgreSQL through the PDO extension
  4. Mysqli: The ext/mysqli driver
  5. Pgsql: The ext/pgsql driver
  6. Sqlsrv: The ext/sqlsrv driver

Question: Can we create a new Adaper for database connection? If yes, How?
With use of following you can create your own Database adapter.
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\ResultSet\ResultSet;
See Example:
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\ResultSet\ResultSet;

class Zend\Db\Adapter\Adapter {
    public function __construct($driver, PlatformInterface $platform = null, ResultSet $queryResultSetPrototype = null)

Question: How to custom query in zend framework 2?
$adapter->query('SELECT * FROM `users` WHERE `embid` = ? and name like "%?%" ', array(5,'rajesh'));

Question: How to join two tables in Zend Framework2?
use Zend\Db\Sql\Select();
use Zend\Db\ResultSet\ResultSet();

$select = new Select();
   ->columns(array('users.*', 'u_name' => 'users.first_name'))
   ->join('profile', 'profile.user_id' = ''); //This is inner Join

$statement = $dbAdapter->createStatement();
$select->prepareStatement($dbAdapter, $statement);
$driverResult = $statment->execute();

$resultset = new ResultSet();
$resultset->initialize($driverResult); // can use setDataSource() for older ZF2 versions.

foreach ($resultset as $row) {

Question: How to use Expression with query in ZF2?
new \Zend\Db\Sql\Expression("NOW()");

Question:How to Add Sub Query in ZF2
$sql = new Sql($this->_adapter);
$mainSelect = $sql->select()->from('table1');
$subQry = $sql->select()
        ->columns(array('orderCount' => new \Zend\Db\Sql\Expression('COUNT(table2.parent_id)')))
        ->where('table2.parent_id =');
            'total' => new \Zend\Db\Sql\Expression('?', array($subQry)),

$statement = $sql->prepareStatementForSqlObject($mainSelect);
$comments = $statement->execute();
$resultSet = new ResultSet();
foreach ($resultset as $row) {

Question: How to use Group By in ZF2
$select = new Select();
   ->columns(array('users.*', 'u_name' => 'users.first_name'))->group('users.first_name');

Question: How to use having clause in ZF2
$select = new Select();
   ->columns(array('users.*', 'u_name' => 'users.first_name','similar_name'=>'count(first_name)'))->group('users.first_name')->having('count(first_name)>1');

Question: How to use Order By in ZF2

$select = new Select();
   ->columns(array('users.*', 'u_name' => 'users.first_name'))->order('users.first_name asc');

Question: How to use limit in ZF2

$select = new Select();
   ->columns(array('users.*', 'u_name' => 'users.first_name'))->order('users.first_name asc')->limit(20);