BIP KB:
PHP Data Objects

PHP Data Objects (PDO) PHP Data Objects, also known as PDO, is an interface for accessing databases in PHP/aithout tying code to a specific database. Rather than directly calling mysql_, mysqli_, and pg_ functions, developers can use the PDO interface, simplifying the porting of applications to other databases.

Database Support

The extension can support any database that a PDO driver has been written for. At the time of this writing, the following database drivers are available:

• PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
• PDO_FIREBIRD ( Firebird/Interbase 6 )
• PDO_IBM ( IBM DB2 )
• PDO_INFORMIX ( IBM Informix Dynamic Server )
• PDO_MYSQL ( MySQL 3.x/4.x/5.x )
• PDO_OCI ( Oracle Call Interface )
• PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
• PDO_PGSQL ( PostgreSQL )
• PDO_SQLITE ( SQLite 3 and SQLite 2 )
• PDO_4D ( 4D )

All of these drivers are not necessarily available on your system; here's a quick way to find out which drivers you have:

print_r(PDO::getAvailableDrivers());

PHP Data Objects usage example

$dsn = 'mysql:dbname=database_name;host=localhost';
$dbuser = 'database_user';
$dbuserpw = 'database_user_password';

try
{
    $connection = new PDO($dsn, $dbuser, $dbuserpw1);
}
catch (PDOException $e)
{
    echo 'There was a problem connecting to the database: ' . $e->getMessage();
}

$query = $connection->query("SELECT * FROM table"); // querying the database

Connecting

Different databases may have slightly different connection methods. Below, the method to connect to some of the most popular databases are shown. You'll notice that the first three are identical, other then the database type - and then SQLite has its own syntax.

try {
  # MS SQL Server and Sybase with PDO_DBLIB
  $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");
  $DBH = new PDO("sybase:host=$host;dbname=$dbname, $user, $pass");

  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

  # SQLite Database
  $DBH = new PDO("sqlite:my/database/path/database.db");
}
catch(PDOException $e) {
    echo $e->getMessage();
}

Please take note of the try/catch block - you should always wrap your PDO operations in a try/catch, and use the exception mechanism - more on this shortly. Typically you're only going to make a single connection - there are several listed to show you the syntax. $DBH stands for 'database handle' and will be used throughout this tutorial. You can close any connection by setting the handle to null.