In this second part of a five-part series on PDO, you'll learn about PDO's database support, how to connect to a database server using PDO, and much more. This article is excerpted from chapter 23 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
PDO’s Database Support
As of the time of this writing, PDO supported nine databases, in addition to any database accessible via FreeTDS and ODBC:
Firebird: Accessible via theFIREBIRDdriver.
FreeTDS: Accessible via theDBLIBdriver. Not a database but a set of Unix libraries that enables Unix-based programs to talk to MSSQL and Sybase.
IBM DB2: Accessible via theODBCdriver.
Interbase 6: Accessible via theFIREBIRD driver.
Microsoft SQL Server: Accessible via theODBC driver.
MySQL 3.X/4.0: Accessible via theMYSQLdriver. Note that at the time of this writing, an interface for MySQL 5 was not available. One can only imagine this is high on the developers’ priority list and will be resolved soon.
ODBC v3: Accessible via theODBCdriver. Not a database per se but it enables PDO to be used in conjunction with any ODBC-compatible database not found in this list.
Oracle: Accessible via theOCI driver. Oracle versions 8 through 10g are supported.
PostgreSQL: Accessible via thePGSQLdriver.
SQLite 2.X and 3.X: Accessible via theSQLITEdriver.
Sybase: Accessible via theODBCdriver.
Tip You can determine which PDO drivers are available to your environment either by loading phpinfo()into the browser and reviewing the list provided under the PDO section header, or by executing thepdo_drivers()function like so: <?php print_r(pdo_drivers()); ?>.
Connecting to a Database Server and Selecting a Database
Before interacting with a database using PDO, you need to establish a server connection and select a database. This is accomplished through PDO’s constructor. Its prototype follows:
The Data Source Name (DSN) parameter consists of two items: the desired database driver name, and any necessary database connection variables such as the hostname, port, and database name. Theusername andpasswordparameters specify the username and password used to connect to the database, respectively. Finally, thedriver_optsarray specifies any additional options that might be required or desired for the connection. Refer to the PHP manual for more information about these options.
You’re free to invoke the constructor in any of several ways, which are introduced next.
Embedding the Parameters into the Constructor
The first way to invoke the PDO constructor is to embed parameters into it. For instance, it can be invoked like this (Oracle-specific):
$dbh = new PDO("OCI:dbname=//localhost/xe", "chapter23", "secret");
Alternatively, if you’d like to connect to a database defined intnsnames.ora, you can just reference the database name like so:
$dbh = new PDO("OCI:dbname=xe");
Placing the Parameters in a File
PDO utilizes PHP’s streams feature, opening the option to place the DSN string in a separate file, residing either locally or remotely, and references it within the constructor, like so:
$dbh = new PDO("uri:file://usr/local/oracle.dsn");
Make sure that the file is owned by the same user responsible for executing the PHP script and that the user possesses the necessary privileges.
Referring to the php.ini File
It’s also possible to maintain the DSN information in thephp.inifile by assigning it to a configuration parameter namedpdo.dsn.aliasname, wherealiasnameis a chosen alias for the DSN that is subsequently supplied to the constructor. For instance, the following example aliases the DSN toocipdo:
