Categories
PHP

Connecting Different Databases Via PDO

PDO (PHP Data Objects) defines a lightweight, consistent interface for accessing many different SQL databases in PHP. You can write and execute database queries with a single interface regardless of the particular database system we happen to be using at the time.

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data.

This is a great advantage that you can easily migrate from one database type to another by rewriting only a small amount of code and changing the existing database drivers for PDO and continuing with business as usual.

As of this writing, the following databases are supported:

  1. Firebird – Connecting Firebird via PDO
  2. MySQL – Connecting MySQL/MariaDB via PDO
  3. Oracle – Connecting Oracle via PDO
  4. PostgreSQL – Connecting PostgreSQL via PDO
  5. SQLite – Connecting SQLite via PDO
  6. CUBRID
  7. IBM
  8. Informix
  9. MS SQL Server
  10. ODBC and DB2
  11. Sybase

Connecting to a Database using PDO

You can establish a database connection by creating an instance of the PDO class:

<?php
 $dbh = new PDO ($dsn, $username, $password, $options);

 // OR
 $dbh = new PDO ($dsn, $username, $password);

 // OR
 $dbh = new PDO ($dsn);

Note: dbh stands for the database handle.

The first step is to initialize an instance of the PDO class and pass the object constructor one, three, or four arguments:

  1. DSN (Data Source Name): indicates the type of database to connect to as well as other database-specific options
  2. Username (optional): The username for the database.
  3. Password (optional): The password for the database.
  4. Options (optional): a key=>value array of driver-specific connection options.

The username and password arguments are optional for some databases.

Handling Connection Errors

If there are any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition:

<?php
 try {
  $dbh  = new PDO($dsn, $username, $password);
 } catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage() . '<br>';
  echo 'Code: '. $e->getCode() .'<br>';
  echo 'Line: '. $e->getLine() .'<br>';
  exit;
 }

See https://php.net/manual/class.pdoexception.php.

Data Source Name (DSN)

A data source name (DSN) is a string that has an associated data structure used to describe a connection to a data source (database server). The DSN string varies from database to database, the following list shows some common DSNs:

MySQL
'mysql:host=localhost; port=3306; dbname=test; charset=utf8'

PostgreSQL
'pgsql:host=localhost; port=5432; dbname=test; user=brain; password=bell'

SQLite
'sqlite:path/test.qlite'

Oracle
'oci:dbname=test'
OR
'oci:dbname=//localhost:1521/test'

Firebird
'firebird:dbname=/path/test.fsb'

Connecting to MySQL/MariaDB

The DSN (Data Source Name) for MySQL/MariaDB uses the following elements delimited by semicolons:

<?php
 $dsn = 'mysql:host=localhost; port=3306; dbname=test; charest=UTF8';
  • Prefix: mysql:
  • host: The hostname (use localhost or 127.0.0.1 if the database server is on the same machine).
  • port: Database server port
  • dbname: Database name
  • charset: Character set

You can skip the port number if the MySQL server is listening on its default port (which is 3306).

Example: Connecting MySQL/MariaDB via PDO

<?php
 $username   = 'brain';
 $password   = 'bell';
 $dsn  = 'mysql:host=localhost;dbname=personal';
 $dbh  = null; //database handler

try {
 $dbh  = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
 echo "Connection failed: " . $e->getMessage();
 exit;
}

Example: Using Unix Socket

To connect through a socket you must not use the host option from the dsn and specify the socket path instead, see the following example:

<?php
 $dsn = 'mysql:dbname=test;unix_socket=/path/socket';

Connecting to PostgreSQL

The DSN (Data Source Name) for PostgreSQL uses the following elements delimited by semicolons (or spaces):

<?php
 $dsn = 'pgsql:host=localhost; port=5432; dbname=test; user=brain; password=bell ';
  • Prefix: pgsql:
  • host: The hostname (use localhost or 127.0.0.1 if the database server is on the same machine).
  • port: Database server port
  • dbname: Database name
  • user: DB username
  • password: DB password

Example: Connecting PostgreSQL via PDO

<?php
 $dsn = 'pgsql:host=localhost; port=5432;
        dbname=test; user=brain; password=bell';
 $dbh = null;

 try {
  $dbh = new PDO($dsn);
 } catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
  exit;
 }

Note: Make sure the PostgreSQL driver pdo_pgsql is uncommented ;extension=pdo_pgsql in the php.ini file: 

extension=pdo_pgsql

Connecting to SQLite

The DSN (Data Source Name) for SQLite uses the following elements :

<?php
 $dsn = 'sqlite:path/to/db.sqlite';
  • Prefix: sqlite:
  • and append the absolute path of the database file has to be appended to the DSN prefix.

Example: Connecting SQLite via PDO

<?php
 $dsn = 'sqlite:/path/dbFileName.sqlite';
 $dbh = null;

 try {
  $dbh = new PDO($dsn);
 } catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
  exit;
 }

Note: Make sure the SQLite driver pdo_sqlite is uncommented ;extension=pdo_sqlite in the php.ini file: 

extension=pdo_sqlite

See How to enable/use SQLite on your server.

Connecting to Oracle

Example: Connecting Oracle via PDO

<?php
 // Connect to a database defined in tnsnames.ora file
 $dsn = 'oci:dbname=personal';

//OR connect using the Oracle Instant Client
$dsn = 'oci:dbname=//localhost:1521/personal';

$username = 'brain';
$password = 'bell';
$dbh = null;

try {
 $dbh = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
 echo 'Connection failed: ' . $e->getMessage();
 exit;
}

Note: Make sure the Oracle driver pdo_oci is uncommented ;extension=pdo_oci in the php.ini file: 

extension=pdo_oci

Connecting to Firebird

The DSN (Data Source Name) for Firebird uses the following elements:

<?php
 $dsn = 'firebird:dbname=path/to/db.fbd;';
  • Prefix: firebird:
  • dbname: Database name
  • charset: Character set
  • role: SQL role name
  • dialect: 1 or 3, the default value is 3.

Specifying the host:

<?php
 $dsn = 'firebird:dbname=192.168.0.99:/path/test.fdb';

Specifying the host and port number:

<?php
 $dsn = 'firebird:dbname=localhost/3050:/path/test.fdb';

Example: Connecting Firebird via PDO

<?php
 $username = 'brain';
 $password = 'bell';
 $dsn = 'firebird:dbname=/path/dbFileName.ext';
 $dbh = null;

 try {
  $dbh = new PDO($dsn, $username, $password);
 } catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
  exit;
 }

Note: If you’re facing difficulty while connecting to the FireBird database, make sure the pdo_firebird driver is enabled (uncommented) in the php.ini file:

extension=pdo_firebird

Connecting a Database on Listening on a Different Port

For localhost, you need to use 127.0.0.1 if the database server is listening on a different port other than the default port. See the following example to specify a database port:

<?php
 $dsn = 'mysql:dbname=test; host=127.0.0.1; port=3311';

Note: you don’t need to specify the port in the DSN if the database server is running on the default port.

Closing PDO Connection

Destroy PDO object by assigning NULL to the variable that holds the object:

<?php
 $dbh = new PDO($dsn, $user, $password);
 //...
 //...
$dbh = null

Also destroy all other references to PDO instance, such as PDOStatement instance:

<?php
 $dbh = new PDO($dsn, $user, $password);
 $sth = $dbh->query('SELECT * FROM table_1 LIMIT 5');
 //...
 //...

 //Close connection
 $sth = null;
 $dbh = null;

Destroy all other variables references, for example, when PDO reference travels across functions and object properties:

<?php
 $dbh = new PDO($dsn, $user, $password);
 $dbh_2 = $dbh;
 $dbh_3 = $dbh_2;
 //...
 //...
 $dbh   = NULL;
 $dbh_2 = NULL;
 $dbh_3 = NULL;

Working with Databases: