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:
This is because I enabled only these three extensions in my php ini file. In reality, PDO supports the following databases:
- DBLIB: FreeTDS / Microsoft SQL Server / Sybase
- Firebird
- IBM (IBM DB2)
- INFORMIX – IBM Informix Dynamic Server
- MySQL
- OCI: Oracle Call Interface
- ODBC: ODBC v3 (IBM DB2 and unixODBC)
- PGSQL: PostgreSQL
- SQLITE: SQLite 3.x
Also, in my version of PHP, the following extensions are available to use with PDO:
;extension=php_pdo_firebird.dll;extension=php_pdo_mssql.dll;extension=php_pdo_mysql.dll;extension=php_pdo_oci.dll;extension=php_pdo_oci8.dll;extension=php_pdo_odbc.dll;extension=php_pdo_pgsql.dll;extension=php_pdo_sqlite.dll
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:
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.
