Database Abstraction with PDO 

In PHP, if you want to connect to a MySQL database server, you use the function mysql_connect( ). If you want to connect to an MS SQL Server, you use mssql_connect( ). To issue queries against the databases you use the functions mysql_query( ) and mssql_query( ), respectively. What if you want to make your PHP application portable across databases? A haphazard solution would be a 'find and replace' or else you wrap the database-specific functions within your own generic functions such as connect( ) and query( ). Besides these, several open-source solutions exist to achieve such database abstraction, ADODB

(http://adodb.sourceforge.net) being one of the popular choices.

PHP always lacked an out-of-box library for database abstraction, but only until the advent of PDO (PHP Data Objects). PDO earlier existed as a PEAR package (plugin libraries for PHP). But with PHP 5.1, it is a part and parcel of the download bundle. In this article, we take you through installing PHP 5.1 on PCQLinux 2005. Subsequently we see a how to use PDO for database abstraction and look at a couple of its features which makes querying any database more structured and easy for a developer. 

Installation



As an example, we will see how to connect to MS SQL Server running on a Windows machine and MySQL server running on PCQLinux 2005 using PDO. To compile MS SQL support in PHP, you will need to download and install the stable release of FreeTDS drivers available from www.freetds.org. Extract the archive, change to the resultant directory and do a 'make' and then 'make install'.

This will install the FreeTDS drivers in /usr/local. Next, download PHP 5.1 package from www.php.net. Note that PHP 5.1 was in the RC1 (release candidate 1) stage as of this writing. Login as root and extract the archive. This will produce a directory named php-5.1.0RC1. Change to this directory and issue the following.

./configure —prefix=/usr --with-apxs2 --with-pdo-mysql --with-pdo-dblib=/usr/local --with-xmlrpc



make


make install

Note that we have only compiled in PDO support (drivers) for MySQL (using the —with-pdo-mysql option) and for MS SQL Server (using the —with-pdo-dblib) option. Depending on your requirement, you may like to compile in support for other databases. Issue './configure --help' which will list down the options to compile PDO drivers for other databases. Next, fire up a text editor and type in the following.

AddType application/x-httpd-php .php



DirectoryIndex index.php

Save the file as php.conf in/etc/httpd/conf.d. In case you have installed PHP that comes with PCQLinux 2005, you will already have a file named php.conf in this directory. In that case, open the file and comment the following line by prefixing a #.

LoadModule php4_module modules/libphp4.so

Restart Apache Web server.

Hello PDO



Below is the code to connect to the MySQL database.

$dsn = "mysql:dbname=pcqdb;host=localhost";



$user = "root";


$password = "secret";





try {


$conn = new PDO($dsn, $user, $password);


} 


catch (PDOException $e) {


print("Error while connecting - " . $e->getMessage());


exit(); 


}


print("Successfully Connected to the database");


?> 

Let's skim through the above code. For each database connection, the only entry that will be different will be the DSN (Data Source Name). In case of MySQL, the DSN is a string with the following syntax.

mysql:dbname=;host=

Next a connection is established by creating a new PDO object. The syntax is as follows:

$conn = new PDO(“”, “”, “ ”);

Here and are the credentials required to access the database. PDO takes advantage of the object oriented and exception handling capability of PHP 5. Hence, we can have a try-catch block to detect and handle connection errors as opposed to using a rudimentary if-then block. $e->getMessage( ) provides detailed connection error information about why the database connection failed for debugging. To execute the above code, substitute the database, user and password with the values in your case and save the above content in a file pdo.php (say) in /var/www/html. Access it using the URL

http:///pdo.php

in the Web browser. >

To connect to a MS SQL Server running at the IP 192.168.1.1 (say), you need to change only the DSN as follows.

$dsn = "dblib:dbname=pcqdb;host=192.168.1.1";

Substitute the database, user and password according to that for MS SQL Server. The rest of the code remains unchanged. 

Prepared query to database



Database abstraction is only one of the features of PDO. Another very useful facility that PDO provides is prepared statements. Using the traditional method, you would issue a query against the database as:

$query = “insert into table1 (name) values (shekhar)”;



$query_result = mysql_query($query,$conn);

But alas, the above query will bomb with a database error. Why? Because the column 'name' of table1 is of type varchar and we missed putting single quotes around the value shekhar. This is one of the situations where a prepared statement comes to help. It allows you to specify the column type while constructing the query. The code below shows how to construct prepared statements with

PDO.

$value="shekhar";



$query = "insert into table1 (name) values (:name)";


$stmt = $conn->prepare($query);


$stmt->bindParam(":name", $value, PDO_PARAM_STR);


$stmt->execute();

While constructing the query, instead of specifying the value, we specify ':name'. With $conn->prepare( ) we prepare a SQL statement corresponding to the query-the function returns a PDOStatement object. With the bindParam( ) function we actually specify the type and value of the column. The syntax is:

$stmt->bindParam(":name", , );

With this statement, :name gets substituted by shekhar and since we have specified the type as PDO_PARAM_STR (a string), the value is enclosed within single quotes. In case the column type in numeric, you can use PDO_PARAM_INT, instead. Finally stmt->execute( ) dispatches the prepared statement or query to the database.The above code remains the same for any other database except for the change in DSN as mentioned in the previous section. Also note that using prepared statements is recommended because it results in an overall increase in the speed of query executions especially if you are firing same queries with different values. 

Displaying the results



If you had a select query or a query that returns a result from the database, then displaying the result using PDO is logically similar to the traditional methods, as shown below.

$query = "select name from table1";



$stmt = $conn->prepare($query);


$stmt->execute();


while ($row = $stmt->fetch(PDO_FETCH_ASSOC))


print($row<"name">.”
”);

Another good feature of PDO is the facility to preset whether the column names in the result array should be in upper case or lower case. This saves a lot of time in going to and fro from the database figuring out the case of the column names. Add the following to the beginning of the code.

$conn->setAttribute(PDO_ATTR_CASE,

PDO_CASE_LOWER);

And you will see the result even if the column 'name' is specified as 'NAME' in the database table. Similarly you can use PDO_CASE_UPPER to have upper case column names. Refer to the these URLs to explore more:

http://www.php.net/manual/en/ref.pdo.php;

http://www.oracle.com/technology/pub/articles/php_experts/otn_pdo_oracle5.html.

Shekhar Govindarajan, IT4Enterprise