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);

Best Related Posts are Following:

1. Zend Soap Client and Server.
2. Zend Framework 2 Http Client Curl - Zend\Http\Client.
3. Zend Framework 2 form elements Filters and Validators.
4. Zend Framework 2 Paginator Hydrator and Basic MVC.
5. Zend Framework 2 Zend Email and Zend _Log.
6. Zend Framework 2 Authentication and Authorization.
7. Zend Framework 2 Service Manager.
8. Zend Framework 2 Json Model understanding.
9. Zend Framework2 manage layout and view variables from controller.
10. Zend Framework 2 Session Management - Add Update Delete Session.
11. Zend Framework 2 get params from URL.
12. How to enable error reporting in Zend Framework 2?.