PHP Data Objects - PHP

There is another process that you can use to access database information. It is a database extension called PDO (PHP Data Objects), and the php.net web site had this to say about it:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

This new product addition and enhancement was scheduled for release in Version 5.1 and should be in general use by the time you are reading this. Basically, this is another approach to connecting to databases in an abstract way. Though similar to the PEAR::DB approach that we have just covered, it has (among others) these unique features:

1. PDO is a native C extension.
   
2. PDO takes advantage of latest PHP 5 internals.
   
3. PDO uses buffered reading of data from the result set.
   
4. PDO gives common DB features as a base.
   
5. PDO is still able to access DB-specific functions.
   
6. PDO can use transaction-based techniques.
   
7. PDO can interact with LOBS (Large Objects) in the database.
   
8. PDO can use Prepared and executable SQL statements with bound parameters.
   
9. PDO can implement scrollable cursors.
   
10. PDO has access to SQLSTATE error codes and has very flexible error handling.

Since there are a number of features here, we will only touch on a few of them to show you just how beneficial PDO is purported to be.

First, a little about PDO. It will have drivers for almost all database engines in existence, and those drivers that PDO does not supply should be accessible through PDO's generic ODBC connection. PDO is modular in that it has to have at least two extensions enabled to be active: the PDO extension itself and the PDO extension specific to the database to which you will be interfacing. See the online documentation to set up the connections for the database of your choice at http://ca.php.net/pdo. For establishing PDO on a windows server for MySQL interaction, simply enter the following two lines into your php.ini file and restart your server:

  extension=php_pdo.dll 
  extension=php_pdo_mysql.dll

The PDO library is also an object-oriented extension (you will see this in the code examples that follow).

Making a connection

The first thing that is required for PDO is that you make a connection to the database in question and hold that connection in a connection handle variable, as in the following code:

  $ConnHandle = new PDO ($dsn, $username, $password);

The$dsn  stands for the data source name, and the other two parameters are self-explanatory. Specifically, for a MySQL connection, you would write the following code:

  $ConnHandle = new PDO('mysql:host=localhost;dbname=library',
   'petermac', 'abc123');

Of course, you could (should) maintain the username and password parameters as variable-based for code reuse and flexibility reasons.

Interaction with the database

So, once you have the connection to your database engine and the database that you want to interact with, you can use that connection to send SQL commands to the server. A simple UPDATE statement would look like this:

  $ConnHandle->query("UPDATE books SET authorid=4 "
   . "WHERE pub_year = 1982");

This code simply updates the books table and releases the query. This is how you would usually send non-resulting simple SQL commands (UPDATE, DELETE, INSERT) to the database through PDO, unless you are using prepared statements, a more complex approach that is discussed in the next section.