This tutorial will show you how to connect a database using Doctrine DBAL.  The examples are for Erdiko, but could be applied to any PHP framework that uses composer.

Installation

If you have not installed Erdiko, please go to http://erdiko.org/getStarted.html#installation

To install Erdiko via Composer. simply run

composer create-project erdiko/erdiko my-project-name

After you have installed Erdiko (or your other favorite framework), it is very easy to install Doctrine.

via composer:

composer require doctrine/dbal 2.3.*

Alternatively, you can do it by hand by modifying the composer.json file in the root folder.  You just need to add this line:

{“require”: {“doctrine/dbal”: “2.3.4”}}

Then run ‘composer update’ to install Doctrine DBAL.

Basic usage

1. Getting a connection

We can get a connection through the DoctrineDBALDriverManager class.

$connectionParams = array(
    ‘dbname’ => ‘database_name’,
    ‘user’ => ‘user_name’,
    ‘password’ => ‘user_password’,
    ‘host’ => ‘localhost’,
    ‘driver’ => ‘pdo_mysql’
);
$conn = DoctrineDBALDriverManager::getConnection($connectionParams, $config);

Now, you are ready to retrieve and manipulate data.

2. Data Retrieval And Manipulation

After you have established a connection with database, it is easy to manipulation data.

In this tutorial, we create a table “Products" and create three fields for it.

The three fields are Name, Qty, and Price.

Inserting Data

$sql = “INSERT INTO Products (Name, Qty, Price) VALUES (‘Mango’, ’10’,5)”;
$stmt = $conn->query($sql);

Retrieving Data

$sql = “SELECT * FROM Products”;

$stmt = $conn->query($sql);

while ($row = $stmt->fetch()) {

    echo $row[‘Name’].’, Qty: ’.$row[‘Qty’].’, Price: ’.$row[‘Price’];

}

The output should be “Mango, Qty:10, Price:5”.

Advanced usage:

To perform fancy data manipulation or query, we will need to set up the class loader before establishing a connection.

Setting up class loader

We can open the Example controller(Example.php) under Erdiko/app/controllers/.

In the Example controller, we will need to add the following line before the class scope and it will allow the program to access the class ClassLoader.

use DoctrineCommonClassLoader;

Then, we can add the following code to a page.

$classLoader = new ClassLoader(‘Doctrine’);

$classLoader->register();

$config = new DoctrineDBALConfiguration();

Note: 

It is not a good practice to set connection parameters every time we get a connection with database.  A better way to do it would be storing all these parameters to a config file and creating a data model to read the config file.

For example, we can create a db.json config file under Erdiko/app/config/local/

In the db.json, we can set the connection parameters.

{

      “data":{       

          “dbname": ‘database_name’,

          “user": ‘user_name’,

          “password": ‘user_password’,

          “host": ‘localhost’,

          “driver": ‘pdo_mysql’

     }

}

Then, we can create a data model and it should looking something like below.

public function getDbConfig($dbConfig)

{

     $config = Erdiko::getConfig(“local/db”);

     $connectionParams = array(

         ‘dbname’ => $config[“data"][“dbname”],

         ‘user’ =>  $config[“data"][“user”],

         ‘password’ =>  $config[“data"][“password”],

         ‘host’ =>  $config[“data"][“host”],

         ‘driver’ =>  $config[“data"][“driver”],

     );

     return $connectionParams;

}

The getDbConfig function will make the program easy to maintain and also reduce the amount of code.

Now, getting a connection will be only required the following line:

$conn = DoctrineDBALDriverManager::getConnection($connectionParams, getDbConfig(‘data’));composer create-project erdiko/erdiko project-name