Working with PDO - PHP Data Objects

Introduction

In the previous examples we used the mysqli extension to connect to a MySQL database. What do you do as a developer if you need to use PHP to connect to another database type other than MySQL? PHP has always been associated with MySQL but there are other function sets in PHP to allow developers to connect to other database types such as Oracle, SQLLite, PostgreSQL and SQLServer. Each different database type has its own specific extensions and therefore slightly different syntax.

This could make life very difficult if you have to change a large application from one database type to another. It would be much better if there was some level of abstraction that allows us to write the same code for our database queries whatever the backend database in use.

PDO to the Rescue

Enter stage right - PHP Data Objects – PDOs. PDO provides a way to connect to a whole range of different database types. Once the connection is made to the database, the queries you write are all done in exactly the same way whatever the underlying database technology. This is done through the use of something called a DSN – Data Source Name. It is the DSN that indicates to PHP the database type you wish to use through the declaration of the specific driver type.

Before we go any Further

Like with the mysqli extension you need to be using PHP5 – in fact you need PHP5.1. If you are still working with PHP4 this is another compelling reason to upgrade. You also need to ensure that the database drivers that you need are enabled on your web host. Using phpinfo() is a good way to check their availability. I might have just been lucky but with the two hosts I have used this technology on so far – 'it just worked' – so I hope that is indicative of the roll out of PHP5.

Creating the PHP Data Object

The key thing with PDO is the creation of a PHP Data Object – which is our gateway to the database. Creating the PHP Data Object for each database type differs only in the syntax used for the creation of the DSN. Once created the PHP Data Object is referenced query creation is performed in a consistent way.

The following examples illustrate how the different PDO drivers are declared.

For SQLLite connection.

$dsn = 'sqlite2:"D:\sqlite\ myDb.sq3"';
$pdo = new PDO($dsn);

See http://www.php.net/manual/en/ref.pdo-sqlite.connection.php for more details.

For postgresSQL

$dsn = 'pgsql:host=localhost port=5432 dbname=myDb user=userName password=xxxx
$pdo = new PDO($dsn);

See http://www.php.net/manual/en/ref.pdo-pgsql.php for more details.

For Oracle

$dsn = oci:dbname= myDb';
$user = 'userName';
$password = 'xxxx';
$pdo = new PDO($dsn, $user, $password);

See http://www.php.net/manual/en/ref.pdo-oci.php for more details.

For MSSQL

$dsn = 'sqlsrv:Server=localhost;Database= myDb';
$user = 'userName';
$password = 'xxxx';
$pdo = new PDO($dsn, $user, $password);

See http://www.php.net/manual/en/ref.pdo-sqlsrv.connection.php for more details.

For MySQL

$dsn = 'mysql:host=localhost;dbname=myDb';
$user = 'userName';
$password = 'xxxx';
$pdo = new PDO($dsn, $user, $password);

See http://www.php.net/manual/en/ref.pdo-mysql.php for more details.

All of the above could be framed in a try/catch combination to allow an error to be displayed with the connection information is incorrect.

The following is a MySQL connection with the try/catch.

<?php
$dsn = 'mysql: host=localhost; dbname=myDb';
$user = 'userName';
$password = 'xxxx';
try {
$pdo = new PDO($dsn, $user, $password);
$pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
?>

In all the examples the DSN is key. As with the likes of mysqli it makes sense to place this code in an include so that it can be referenced by all the files in your application.

4 Comments

  1. Chris Pink
    Apr 29, 2013 @ 20:31:24

    Ih the last code sample the inclusion of a space before the database name dbname= myDb causes an error. It needs to be without a leading space (PHP 5.4.4)

    Reply

    • admin
      Apr 29, 2013 @ 21:10:18

      Chris - Thanks for letting me know - I'll update it.
      Martin

      Reply

  2. Chris Pink
    Apr 29, 2013 @ 20:32:01

    Apart from that this is the first PDO tutorial that makes sense! Thank you.

    Reply

  3. bob
    Sep 21, 2015 @ 09:36:24

    This tutorial is brilliant. I'm new to PDO and it;s exactly how Chris describes it!! Thank you

    Reply

Leave a Reply to Chris Pink Cancel

*