Using PDO Objects in PHP 5 - Using the PDO extension

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.