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(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ),
),
'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();
$select->from('users')
   ->columns(array('users.*', 'u_name' => 'users.first_name'))
   ->join('profile', 'profile.user_id' = 'users.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) {
print_r($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()
        ->from('md_type')
        ->columns(array('orderCount' => new \Zend\Db\Sql\Expression('COUNT(table2.parent_id)')))
        ->where('table2.parent_id = table1.id');
$mainSelect->columns(
        array(
            'id', 
            'total' => new \Zend\Db\Sql\Expression('?', array($subQry)),
        )
);

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



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



Question: How to use having clause in ZF2
$select = new Select();
$select->from('users')
   ->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();
$select->from('users')
   ->columns(array('users.*', 'u_name' => 'users.first_name'))->order('users.first_name asc');



Question: How to use limit in ZF2

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