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 ?
- Pdo_Mysql: MySQL through the PDO extension
- Pdo_Sqlite: SQLite though the PDO extension
- Pdo_Pgsql: PostgreSQL through the PDO extension
- Mysqli: The ext/mysqli driver
- Pgsql: The ext/pgsql driver
- 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);
