How to use PDO for PHP and Oracle and other DB

The PDO library is a powerful addition to PHP. It shines when you need to build applications capable of talking to different database systems. It also has other very useful capabilities. This article, the first in a three-part series, will introduce you to this versatile library.

Introduction

PHP developers who used to work with different relational database systems have had two choices when it came to building their web applications. They either had to include a third-party library that allowed transparent handling of the interaction with RDBMSes, or, in the worse case, they had to create their own database abstraction layers, which was a time-consuming process.

In the first case, picking up a pre-built package that provided PHP programmers with a decent level of abstraction for using diverse database servers was reduced to choosing between a certain number of well-trusted libraries. These include the robust PEAR::DB, or the excellent ADODb database abstraction software, to name the most popular libraries.

However, in many cases PHP applications are conceived from the very beginning to interact only with MySQL, which facilitates their development enormously. In this situation the application does not need to work with multiple database systems. Consequently, in this case using a simple MySQL-related package can satisfy quickly all the eventual database requirements.

Nonetheless, there are times when a certain web application needs to interact with more than one database server. This implies having to build more complex classes (if you're using an object-oriented approach) or picking up a third-party library that may be capable of suiting these specific database requirements.

As you may have heard, for those PHP developers that frequently need to work with different database systems, I must say that good news are just around the corner. With the release of PHP 5.1 (and subsequent versions of the language, by the way), a brand new database abstraction layer has been included.

Yes, you guessed right! In this case I'm talking about the powerful PDO library. PDO is short for PHP Data Objects. The library consists of a versatile package that makes it easy to talk to popular database systems, such as MySQL, Oracle, MS SQL, and so on using a fully oriented object-based notation. Of course, the benefits of using this library don't end here; it also offers the possibility of processing result sets in all sorts of clever ways, handling stored procedures, performing transactions, using markers in queries, and so forth.

As you may have seen at first glance, the PDO extension looks really useful in certain situations. In this three-part series, I'm going to introduce its most important features, by accompanying the corresponding theoretical concepts with numerous hands-on examples, in this way facilitating the respective learning curve.

Definitely, a good point to start demonstrating the excellent functionality provided by the PDO extension is in showing how it can be used to connect to diverse database systems. Therefore, I coded a simple script that establishes a new connection to MySQL, using the pertinent PDO constructor.

The signature of the script is as follows:

// example connecting to MySQL with the PDO extension
$dbh = new PDO('mysql:host=localhost;dbname=alejandro','user','password');

Certainly, after examining the above script, you'll have to agree with me that connecting to a specific database system, in this case MySQL, is a process that can be easily performed with the PDO library. The procedure is reduced to creating a new instance of the PDO class and passing to its constructor the proper connection parameters.

As you can see, the constructor accepts these incoming arguments in the form of a typical connection string (very similar to the notation used with ODBC databases), where the host is told where to connect, the selected database, and finally that the corresponding user/password combination must be supplied in the proper sequence.

Well, once a successful connection has been established to the specific database server, a new PDO object is returned to client code. This object allows the performance of a great variety of database-related tasks, which will be covered in detail over the new few lines, so don't worry about them for the moment.

Now, let me return to the previous script and show you how to close the connection that was opened to the specified MySQL server. This procedure is achieved as follows:

// example closing a connection to MySQL
$dbh = new PDO('mysql:host=localhost;dbname=alejandro','user','password');

// close MySQL connection by assigning a NULL value to PDO object
$dbh = NULL;

This isn't rocket science at all. As you can see, closing an established connection is performed by assigning a NULL value to the pertinent PDO object, as indicated above. Quite simple, right?

Now that you have learned how to use the PDO extension to open and close a connection to a sample MySQL database server, let me show you how to perform the same tasks with an Oracle system. Here is how these processes are done:

// example connecting to Oracle Call Interface
$dbh=new PDO('oci:','user','password');

// example closing a connection to Oracle Call Interface
$dbh=new PDO('oci:','user','password');

// close Oracle connection by assigning a NULL value to PDO
object
$dbh = NULL;

As you can see, the process of opening and closing a connection to an Oracle database server is nearly identical to the one utilized with MySQL. However, you may want to see more examples of connecting to different database systems. Below I included a few short scripts that demonstrate how to perform these connections. Please take a look.

//**************************************************
// example opening an ODBC connection
$dbh= new PDO('odbc:EXAMPLE','db2inst','ibmdb');

//**************************************************
// example closing an ODBC connection
$dbh= new PDO('odbc:EXAMPLE','db2inst','ibmdb');

// close ODBC connection by assigning a NULL value to PDO object
$dbh = NULL;

//**************************************************
// example opening a connection to SQLITE 2 (available in PHP
5.1)
$dbh= new PDO('sqlite2:/databases/mydb.sq2');

//**************************************************
// example closing a connection to SQLITE 2
$dbh= new PDO('sqlite2:/databases/mydb.sq2');

// close SQLITE2 connection by assigning a NULL value to PDO
object
$dbh = NULL;

As shown above, connecting to MySQL, Oracle or SQLite 2 is reduced to passing the proper connection parameters to the respective PDO constructor, since all of the tasks related to handling a specific database system are performed behind the scenes. Also, it's worthwhile to stress here that the connection strings used with all the previous examples may vary according to the database used. Therefore, I suggest you have a look at the PDO official documentation, located at http://ww.pdo.php.net, for further details.

At this point, you have learned how to connect to different database servers, but you may be wondering what happens if a particular connection fails. Well, the PDO library really shines in this aspect, since if an error occurs, a PDO exception (yes, you read correctly) is thrown, which allows it to easily handle any potential problem with elegance and efficiency.

This process is illustrated by the script below; please examine its signature.

// example using the PDO Exception (an exception is thrown when
an error occurs at connecting or when performing other tasks)
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro',$user,$password);
}
catch(PDOException $e){
echo 'Error connecting to MySQL!: '.$e->getMessage();
exit();
}

As you can see, the above example clearly demonstrates how to use a PDO exception to handle a failed connection. However, the functionality of this proprietary exception mechanism doesn't stop here. Different exceptions will be triggered when performing queries, handling results sets, and so forth, which can be really useful for having all the eventual errors handled by a centralized module.

Okay, now that you have seen how to open and close a connection to different database systems, it's time to examine other methods included with the PDO extension. In this case, I'm going to show you how to run queries against a concrete database, so if you're interested on seeing how this will be achieved, click on the link below and keep reading.

Naturally, after learning how to open and close a connection to a specific database system, I'm sure that you'll want to see how to execute a query against a particular database. To perform this crucial task, the PDO extension comes equipped with two primary methods, not surprisingly called "query()" and "exec()" respectively.

However, the two methods behave slightly differently. The second one, that is "exec()," not only performs a specified query, but returns the number of affected rows after performing an "INSERT," "UPDATE" or "DELETE" command.

Now that you see the difference between both methods, please study the following examples. They demonstrate separately how to use each of the methods in question, first for connecting to MySQL, and then for fetching some rows from a sample "USERS" database.

The respective code listings are as follows:

// example using the 'query()' method
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
foreach($dbh->query('SELECT * FROM users') as $row){
echo 'Name :'.$row['name'].' Postal Address :'.$row['address'].' Email :'.$row['email'].'
';
}
}
catch(PDOException $e){
echo 'Error : '.$e->getMessage();
exit();
}

// example using the 'exec()' method (returns the number of rows
affected by the query)
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
$delrows=$dbh->exec('DELETE FROM users WHERE id<20');
echo 'Number of deleted rows is the
following :'.$delrows;
}
catch(PDOException $e){
echo 'Error : '.$e->getMessage();
exit();
}

As shown previously, the first example illustrates the implementation of the handy "query()" method to fetch some rows from a simple database table, while the second case shows how to use "exec()" to run a "DELETE" statement, and finally display the number of deleted rows.

It's worth clarifying here that all of the above examples use a MySQL server, but as you learned in the previous section, the same methods can be utilized with Oracle, MS SQL, SQLite or whatever database system you need to include in your own PHP applications.

So far, so good. At this stage you have hopefully learned how to run queries with the PDO library utilizing the two primary methods bundled with the extension. So what's the next step? Well, in the last section of this tutorial I'm going to show you how to use a brand new method, called "lastInsertId()," which obviously returns the ID of the last-inserted database row.

To learn how this method can be implemented, go ahead and read the new few lines. I'll be there, waiting for you.
As I pointed out in the section you just read, the PDO extension also offers a handy method, called "lastInsertId()," which is useful in those situations where it's necessary to find out the ID of the last-inserted database row.

The implementation of the method is very straightforward, as you can see in the example below:

// example using the 'lastInsertId()' method (returns the ID of
last inserted row)
try{
$dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
$dbh->query("INSERT INTO users SET
name='Alejandro',address='Nowhere',email=
'alejandro@domain.com'");
$insertId=$dbh->lastInsertId();
echo 'ID of last-inserted row after executing SQL
statement is as following: '.$insertId;
}
catch(PDOException $e) {
echo 'Error : '.$e->getMessage();
exit();
}

Finding the ID of the last-inserted database row is an easy-to-perform task, thanks to the excellent functionality provided by the "lastInsertId()" method. Similar to the approach followed with previous examples, in this case I used the MySQL server to demonstrate how this method works, but as you saw earlier, this condition can be easily modified to work with a different database system.

As usual with many of my articles on PHP development, feel free to introduce your own modifications to all the hands-on examples shown here, so you can acquire a more robust grounding in how to use the most important features offered by the PDO extension. Fun is already guaranteed!

Final thoughts

In this first part of the series, I walked you through the key points of how to use the PDO extension that comes bundled with 5.1 and up. As was demonstrated by the hands-on examples included in this article, this library definitely makes working with multiple database systems a painless process.

Nonetheless, I have to admit that I'm only scratching the surface when it comes to exploring the numerous features offered by PHP Data Objects. So, considering the long way ahead of us, in the next tutorial I'm going to show you how to use this powerful PHP extension to manipulate results sets regardless of the database system you use.

courtsy: devshed.com