PHP Data Objects, or "PDO" as it is commonly known, is a lightweight database abstraction layer that is arguably the best, at least in terms of speed. A great deal of this speed is owing to the fact that the PDO extension was compiled with C/C++. The extension became available in PHP5, and as with any other database abstraction layer, its aim is to provide a uniform interface to access a variety of databases. This is also a way for developers to create portable code for a variety of platforms.
As already stated, PDO supports a variety of databases. I have PHP 5.2 (Windows) installed on my system and can use PDO with the following databases:


Click here for larger image

Figure 1. List of Databases PDO Supports on My System
This is because I enabled only these three extensions in my php ini file. In reality, PDO supports the following databases:
Also, in my version of PHP, the following extensions are available to use with PDO:
To use any of the extensions above, simple open up your PHP ini file and enable the extensions by removing the semicolon (;) from the extension that you want to use. To verify that PHP has loaded the extensions you want, you can run the phpinfo() function, which should produce something like this:


Click here for larger image

Figure 2. Result from phpinfo() Function
The image above shows the PDO section of my PHP installation. Alternatively, you can run the static method of PDO called getAvailabledrivers() to get a list of drivers on your system:

foreach(PDO::getavailabledrivers() as $supportedDB) {
echo $supportedDB.'
'; } ?>
The function should provide you with a list of all the drivers loaded in your PHP.

How Do I Connect to Databases with PDO?

Now that you have verified that the drivers are installed on your system, the next thing is to use them to connect to a database. Let's start with by attempting to connect to a PostgreSQL database. Create a PHP document and add the following code:

Untitled Document
<!--?php
try {
$conh = new PDO("pgsql:dbname=contacts;host=localhost", "YourUsername", "YourPwrd" );
echo "You are connectioned to PgSQL";
}
catch(PDOException $ex)
{
echo $ex->getMessage();
} ?>
For those of you who have used PHP to connect to databases before, the above code should look familiar. So what have we done here? Well, we used the keyword "new" to create a PDO connection object, passing to it the database name, host, username and password. Once connected, the user is informed that a connection has been made. Also note that we've rightly used the try..catch construct to inform us of any connection errors that may occur. It is that easy to connect to a database using PDO. But why stop there? Can we connect to MySQL using PDO? Sure we can:


Untitled Document
<!--?php
//declare mysql database server connection details
$Myhostname = 'localhost';
$Myusername = 'Yourusername';
$password = 'passwrd';
$db = 'mysql';
try {
$handler = new PDO("mysql:host=$host;db=$db", $MyUsername, $MyPasswrd);
//inform user of connection status
echo 'You are connected to MySQL';
}
catch(PDOException $ex)
{
echo $ex->getMessage();
}
?>
As you can see, PDO uses similar methods to connect to a database. We've used the try..catch construct to catch any connection errors as before. In fact, I do not have a database called "db" so I got the following exception message:
SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)
When running the same code without the try..catch construct, we get the following error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)' in C:InetpubwwwrootJava-Formgeneric.php:21 Stack trace: #0 C:InetpubwwwrootJava-Formgeneric.php(21): PDO->__construct('mysql:host=loca…', 'username', 'password') #1 {main} thrown in C:InetpubwwwrootJava-Formgeneric.php on line 21
As stated before, PDO uses similar connection methods regardless of the type of database you are connecting to. There is a slight exception to this rule when it comes to SQLite. This is mainly because SQLite uses a file to connect to, unlike MySQL or PgSQL. To connect to SQLite:

Untitled Document
<!--?php
try {
$handler = new PDO("sqlite:/path/to the/db.sdb");
} catch(PDOException $ex)
{ echo $ex->getMessage();
} ?>
The exception to the rule that I alluded to earlier arises from the fact that you do not pass the usual crop of credentials (such as a username or password) to the PDO connection object; instead, you simply pass the path to the database.

How Do I Run Queries with PDO?

Now that we can make connections to databases of different types, how can we run queries? PDO makes this very easy. To demonstrate, let's start by looking at how to retrieve data from a database using the SELECT statement:

Untitled Document
<!--?php
// declare mysql database server connection details
$Myhostname = 'localhost';
class="code>$Myusername = 'Yourusername';
$password = 'passwrd';
$db = 'mysql';

try {
$handler = new PDO("mysql:host=$host;db="$db, $MyUsername, $MyPasswrd);
//Inform user of connection status
echo 'You are connected to MySQL';

//Define SQL SELECT statement
$getdata = "SELECT * FROM MyTable";
foreach ($handler->query($sql) as $row)
{
echo $row['columname1'] .' - '. $row['columname2'] . '
'; } // close the database connection $handler = null; } catch(PDOException $ex) { echo $ex->getMessage(); } ?>
For those of you who have used PHP to connect to MySQL, the above statements will be very familiar. You will also note that foreach() is used to iterate through the array of records, which is different from the while() loop that is commonly used when accessing arrays using MySQL. This is mainly because PDO uses the Standard PHP Library traversable iterator internally.
Next we look at how to UPDATE a record:

Untitled Document

<!--?php
// declare mysql database server connection details
$Myhostname = 'localhost';
$Myusername = 'Yourusername';
$password = 'passwrd';
$db = 'mysql'; try {
$handler = new PDO("mysql:host=$host;db="$db, $MyUsername, $MyPasswrd);
//Inform user of connection status
echo 'You are connected to MySQL';

//run UPDATE statement
$affected_rows = $handler->exec("UPDATE MyTable SET columnname1='Avalue1' WHERE columname1='Avalue2'");
//show user number of records changed
echo $ affected_rows;
//close the database connection
$handler = null;
} catch(PDOException $ex)
{ echo $ex->getMessage();
} ?>
Notice that this time we've used PDO::exec(). It should be used for queries that do not expect a result set to be returned. To insert a record, simply change:

$affected_rows = $handler->exec("UPDATE MyTable SET columnname1='Avalue1' WHERE columname1='Avalue2'");
To:
$ affected_rows = $handler->exec("INSERT INTO MyTable(columname1, columname2) VALUES ('AValue1', 'AValue2')");

Conclusion

In conclusion, just two of the reasons why you should use PDO in your applications include the following:
PDO is also the best choice when it comes to writing portable database applications, precisely because of its database abstraction.