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: 127.0.0.1
    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:

2015-09-27 11_05_12-Untitled - paint.net 4.0.6Well, that’s of course possible only in case we use same exact names in database and in php class :

<?php

namespace Bisaga\Database\Model;

/**
 * Country table 
 */
class Country {
    /**
     * Row identifier 
     * @var integer auto numbered unique identifier
     */
    public $id;

    /**
     * Country name 
     * @var string country name  
     */
    public $name;
    
    /**
     * Formal country name 
     * @var string 
     */
    public $formal_name;

    /**
     * Country legal type 
     * @var string  
     */
    public $legal_type;
    
    /**
     * Country legal subtype 
     * @var string 
     */
    public $legal_sub_type;

    /**
     * Country sovereignty 
     * @var string  
     */
    public $sovereignty;

    /**
     * Country capitol 
     * @var string 
     */
    public $capitol_city;
    
    /**
     * Currency code 
     * @var string  
     */
    public $currency_code;
    
    /**
     * Country currency name  
     * @var string 
     */
    public $currency_name;

    /**
     * Country telephone code 
     * @var string
     */
    public $telephone_code;
    
    /**
     * Country 2 characters iso code 
     * @var string 
     */
    public $iso2code;

    /**
     * Country 3 characters iso code 
     * @var string 
     */
    public $iso3code;

    /**
     * Country 3 characters numeric iso code 
     * @var string 
     */
    public $iso3num;
    
    /**
     * Country domain name suffix
     * @var string 
     */
    public $domain_code;
    
}

Custom columns mapping

Well, you really need to have a very good reason to select different names for columns in database, but sometimes you are not original owner of database. In that case you could introduce mapping function like this:

 /**
 * Function create instance of target type and fill target properties 
 * 
 * @param object $sourceObject Object instance or array as source 
 * @param string $targetClass Target class name fith full namespace 
 * @param array $mapping  array with mapping names, 
 *                        key represent name of the property in source object, 
 *                        value represent column name in target object 
 * @return any function return object instance of target type 
 */
private function castToClass($sourceObject, $targetClass, $mapping = null)
{
	$target = new $targetClass();
	if (is_array($sourceObject) || is_object($sourceObject)) {
		foreach ($sourceObject as $key => $value) {
			
			if (null === $mapping ) {
				$target->$key = $value;
			}
			elseif ( array_key_exists($key, $mapping) )
			{
				$mapped_name = $mapping[$key];
				$target->$mapped_name = $value; 
			}
		}
	}
	return $target;
}

Prepare sample for mapping

We create new table “currency” in the database  :

create table currency (
    numeric_code int not null,
    currency_code char varying(20) not null,
    primary key (numeric_code),
    key ix_code (currency_code)
)

and write new class “Currency” in php :

<?php

namespace Bisaga\Database\Model;
/*
 * Currency table 
 */
class Currency {
    /**
     * Currency code (840, 978 etc.)
     * @var integer currency numeric value 
     */
    public $id;
    /**
     * Currency code (USD, EUR etc.)
     * @var string currency code 
     */
    public $code;
}

We do not want push to far in this direction, we could start developing a new ORM or something and that is not our intention. We want to retain full power of SQL language in the application, without need to write to much PHP or SQL code manually.

Usage example:
 $stmt = $cn->createQueryBuilder()
                ->select("numeric_code, currency_code")
                ->from("currency")
                ->where("id = :id")
                ->setParameters(array("id"=>852))
                ->execute();
        
 $object = $stmt->fetch();
 /*@var $currency \Bisaga\Database\Model\Currency  */
 $currency = $this->castToClass($object, 
               "\Bisaga\Database\Model\Currency", 
               array("numeric_code" => "id", 
                     "currency_code"=>"code"));

Mapping array contain database columns first (as keys) and class properties second (as values).

Inserting data

Inserting data is straightforward :

$cn->insert("country", (array)$country);
$country->id = $cn->lastInsertId();

Because insert command expect array as second parameter, we simply convert object instance ($country)  to associative array.  After record is inserted, we inspect last inserted value in auto numbered column and return value to the program.

Update data

Update is very similar to insert, except we need to add third parameter to find existing record :

 $cn->update("country", (array)$country, array("id"=>$country->id));

Delete data

And deleting is event simpler:

$cn->delete("country", array("id"=>$country->id));

I didn’t mentioned that all commands are available as query builder methods.

Working with transactions

There is full transaction support directly in php code. We must explicitly start transaction and commit after last database command. In case of errors we need to call rollback on the used connection.

$cn->beginTransaction();
try {
	$cn->insert("country", (array)$country);
	$country->id = $cn->lastInsertId();
	$cn->commit();
} catch (Exception $exc) {
	$cn->rollBack();
	throw $exc;
}

If you think this is little verbose and want to shorten the code, you actually can, just use closure with “transactional” function :

/**
 * @param \Bisaga\Database\Model\Country $country
 */
public function insertWithClosure(\Bisaga\Model\Country $country)
{            
	// use closure form with beginTransaction/commit/rollBack support 
	$this->getConnection()->transactional(function(\Doctrine\DBAL\Connection $cn) use ($country) {
		/* @var $cn Connection */
		$cn->insert("country", (array)$country);
		$country->id = $cn->lastInsertId();
	});
}

 

Installing the php_intl extension

Enabling php_intl extension

If you need internationalization extension (php_intl.dll), and you have similar development environment as I have, then follow this steps:

Open Ampps manager under PHP , enable internationalization extension (php_intl.dll) and click Apply.

2015-09-16 22_41_09-Program ManagerThat was simple , yes ?

To check if extension is available, you need to start “php -i” in command line and search for “intl” :

H:\>php -i

Well, I got this error:

The procedure entry point __crtCreateSymbolicLinkW could not be located in the dynamic link library MSVCR110.dll

The procedure entry point __crtCreateSymbolicLinkW could not be located in the dynamic link library MSVCR110.dll

We need latest Visual C++ Runtime, but even if we have one on the windows installed, something prevent PHP to load correct DLL.

After a while and lot of experimenting I figure out that MSVCR110.dll is installed in PHP folder as part of Ampps installation (H:\Ampps\php folder for me). Windows will load this local DLL from folder where program started and will not search for one on the system.

I just rename dll file to something else (“not_used_MSVCR110.dll”) and check again. This time “php -i” was executed without errors and I found “intl” settings in generated output .

intl

Internationalization support => enabled
version => 1.1.0
ICU version => 51.2
ICU Data version => 51.2

There is one more location where MSVCR110.dll is installed (H:\Ampps\apache\bin). This is obviously inside apache runtime system and I renamed that too.

I am not sure if this is the best way to solve this problem, but for now I am satisfied.

 

Symfony – demo application

Install demo application

Symfony provide demo application as a learning resource.  To install it just enter “demo” as parameter to “symfony” command.

H:\Ampps\www>symfony demo

Start demo application

To start newly installed app, navigate to:

http://localhost/symfony_demo/web/app_dev.php

First page of application :

2015-09-16 21_53_17-Symfony Demo applicationIf demo application doesn’t work as expected and you get error like this :

An exception has been thrown during the rendering of a template (“The Symfony\Component\Intl\DateFormatter\IntlDateFormatter::__construct() method’s argument $locale value NULL behavior is not implemented.  Only the locale “en” is supported. Please install the “intl” extension for full localization capabilities.”) in blog/post_show.html.twig at line 35.

Just repair one line of php code in “IntlDateFormatter.php” file, as described in details in this fix .

File is placed inside demo app source tree , in my case this is:

H:\Ampps\www\symfony_demo\vendor\symfony\symfony\src\Symfony\Component\Intl\DateFormatter\IntlDateFormatter.php

Open file and change first “if” statement in “__constructor” method to reflect second line :

-        if ('en' != $locale) {
+        if ('en' !== $locale && null !== $locale) {

Save file and navigate to application again, this time should work as expected.

Debugging in Ampps and Netbeans

Please read great article about installation and configuration debugging for PHP in Ampps server.

I just want to add some additional tips before you start downloading and installing files…

Determine you visual C runtime, architecture and thread safety

Open Ampps local control center and check PHP Info page.

2015-09-13 23_17_18-phpinfo()

Files are named with combination of used compiler (MSVC11 = VC11, MSVC9 = VC9), architecture (x86 = 32bit,  x64 = 64bit) and thread safety settings (TS = enabled).

With this information you should select proper DLL for download.

After downloading DLL file for your selected PHP configuration, you must copy file to “php/ext” folder and add  new config setting to php.ini file :

zend_extension="\php_xdebug.dll"

For debuging to work from Netbeans, you need to add this xdebug settings to php.ini file:

xdebug.remote_enable=1
xdebug.remote_host=127.0.0.1
xdebug.remote_port=9000
; Port number must match debugger port number in 
; NetBeans IDE Tools > Options > PHP
xdebug.remote_handler=dbgp

Restart Apache and check phpInfo() in local ampps web admin page (http://localhost/ampps/).

2015-09-13 23_31_42-phpinfo()After successful installation your breakpoints in PHP code should work as expected.

2015-09-07 21_41_09-NetBeans Platform 8.0.2

When you debug application, link to the application, get additional debug parameter:

http://localhost/webapp03/web/app_dev.php?XDEBUG_SESSION_START=netbeans-xdebug

 

Symfony installer, creating new project

Symfony installer

Symfony is one of the best php frameworks for web development. To start working with symfony we first need to download symfony installer:

c:\> php -r "readfile('http://symfony.com/installer');" > symfony

After downloading symfony file, you can copy it to your new projects folder.

Then you use it with php interpreter as a command:

C:\> php symfony

If you wish to simplify usage of symfony command,  just create dos batch command file (symfony.bat) with next content:

@echo off
call php C:\cmd\symfony %*

Save both files, downloaded symfony file and batch file to a system wide available folder. If you haven’t it yet, create one folder (like C:\cmd) and add it to system environment PATH.  New command “symfony” will then be available anywhere on the system. To check it, open DOS command window and type symfony command:

H:\TEMP> symfony

You should see something like this:

2015-09-04 17_44_31-Command Prompt

Symfony installer installed !

Create first project

To create new symfony project, open DOS command line in projects parent folder and enter command :

C:\ampps\Ampps\www\>symfony new webapp03

As I described in this blog article, my development environment consist of locally installed apache web server. This means I don’t need to start web server every time I want to serve application manually, but AMPPS application must be started of course.

To open newly created application, enter URL of the project into the browser:

http://localhost/webapp03/web/

You will get application default startup page like:

2015-09-06 10_00_35-Welcome! - Opera

Create project in Netbeans with existing source

If you now want to work in Netbeans, just open new project with existing source:

2015-09-07 20_47_23-New Project

 

Select folder where generated project reside and you are good to go. You can create extra meta data folder for netbeans specific project files. This way you will not to pollute  web folder itself.

2015-09-07 20_48_29-New PHP Project with Existing SourcesOn the last step you should define startup file , this could be web/app.php or web/app_dev.php, with some additional development informations from symfony framework in the bottom of the web page.

2015-09-07 20_49_26-New PHP Project with Existing Sources