Doctrine DBAL - working with database

In this article I am going to cover using MySql database in Symfony 2 application.

I will use the simplest possible database access technology PHP offers - PDO,  but in slightly extended form of Doctrine DBAL. So if you want to use Doctrine ORM , this article is not right place to start.

Before we begin

We need some sample data in the database,  so we create a database “dbsamples” with “country” table and import data from this csv file. Because I will not use full Doctrine ORM ,  generating db schema is not an option, I will create database manually.

create table country ( id int not null auto_increment, name char varying(250) not null, formal_name char varying(250), legal_type char varying(100), legal_sub_type char varying(100), sovereignty char varying(100), capitol_city char varying(100), currency_code char varying(30), currency_name char varying(100), telephone_code char varying(100), iso2code char varying(2), iso3code char varying(3), iso3num char varying(3), domain_code char varying(20), primary key (id), key ix_name (name), key ix_formal_name (formal_name) )

You can use phpMyAdmin for that,  it is usually installed by default. If you need true utf8 database, you need to set character set to utf8mb4.

Connecting to the database

Because we use Symfony standard framework, we already have  connection parameters in a configuration file (“parameters.yml”).

# This file is auto-generated during the composer install parameters:     database_driver: pdo_mysql database_host: database_port: 3306 database_name: dbsamples database_user: dbuser database_password: mypassword     # [20] PDO::ATTR_EMULATE_PREPARES = false, # [17] PDO::ATTR_STRINGIFY_FETCHES = false     database_options: {20: 0, 17: 0}

I added one new parameter here, an array of custom driver options (“database_options”). By default all received data to php are stringified, it means you get all data in strings. To receive native data types from the database (int as integer for example), you need to set those options and use prepared statements. But this options will only work if you have luck and your hosting installed and enabled  “mysqlnd” native driver, check your hosting for the driver type.

In the config.yml file, this parameters are mapped to dbal connection  parameters:

# Doctrine Configuration doctrine: dbal: driver: “%database_driver%” host: “%database_host%” port: “%database_port%” dbname: “%database_name%” user: “%database_user%” password: “%database_password%” charset: UTF8 options: “%database_options%”

When we are in the symfony controller file (as in \AppBundle\Controller\DefaultController.php for example), we get connection directly from the container:

$cn = $this->getDoctrine()->getConnection();

or more specific for DBAL connection :

$cn = $this->container->get(‘doctrine.dbal.default_connection’);

but because aquired connections are not pooled (php connections do not support connection pool yet), is actually the same if you create new connection by yourself :

$config = new \Doctrine\DBAL\Configuration(); $connectionParams = array(‘url’=>‘mysql://dbuser:mypassword@localhost/dbsamples’ ); $cn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

Selecting data

Selecting data in DBAL is easy and intuitive, just use connections executeQuery method. If you use parameters in SQL statement, this will create prepared statement for you and bind parameters automatically. You should always use prepared statements because they are more secure and more efficient as plain text statements.

//create prepared statement and execute it $stmt = $cn->executeQuery(“SELECT * FROM country WHERE id = :id”, array(“id”=>157));

But there is even better way, using QueryBuilder object and assembling SQL statement with PHP code.

$stmt = $cn->createQueryBuilder() ->select("*") ->from(“country”) ->where(“id = :id”) ->setParameters(array(“id”=>157)) ->execute();

Fetch data to types

To get result from a statement, we need to execute one of the “fetch” methods.

$country = $stmt->fetch();

By default we receive each result row as an array, or as array of arrays for multiple records. We can even fetch directly to an instance of an class:

//fetch result to class instance $stmt->setFetchMode(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, “\Bisaga\Database\Model\Country”);

/*@var $country \Bisaga\Database\Model\Country */ $country = $stmt->fetch();

Don’t forget we have all the power of PDO library at our disposal.

With little help from variable type hinting , we get full code completion on received object instance in the IDE:

/images/, that’s of course possible only in case we use same exact names in database and in php class :