12.0 Introduction

One of the most powerful features of PHP is its capability to connect to a database, whether it be MySQL or Oracle. It's the reason why most people use PHP: to take the data in databases and bring it to the Web.

In PHP, programmers traditionally make database-specific calls to connect to a different database. For example, you would use mysql_connect() to connect to a MySQL database, whereas you would have to use the OCILogon() function to connect to an Oracle database.

Using different functions to connect to different databases was a drag for many PHP programmers, especially converted Perl programmers who were used to Perl's DBI, which allows for portability when changing databases. For example, examine the following two pieces of code. One uses PHP's API to connect to MySQL and the other uses Perl's DBI to connect to MySQL:

PHP:

 <?php $dbh = mysql_connect($host, $user, $password); mysql_select_db($dbname); $stmt = "SELECT * FROM table"; $sth = mysql_query($stmt, $dbh); while ($row = mysql_fetch_array($sth, MYSQL_ASSOC)) {     echo $row["firstName"];     echo $row["lastName"]; } mysql_free_result ($sth); mysql_close ($dbh); ?> 

Perl with DBI:

 use DBI; my $dbh = DBI->connect("dbi:mysql:$dbname", $user, $password); my $sth = $dbh->do ("SELECT * FROM table"); while (my $row = $sth->fetchrow_hashref)) {     print $row->{firstName};     print $row->{lastName}; } $sth->finish; $dbh->disconnect; 

As you can see, they are both of approximately the same complexity, but the Perl example is more portable. In PHP, if you wanted to change the database from MySQL to Oracle, you would have to use an entirely different set of functions. However, in the Perl example, you need to change only one line:

 $dbh = DBI->connect("dbi:oracle:$dbname", $user, $pass); 

Perl's DBI uses an object-oriented approach to creating a database-independent API, but this is not the only way to achieve database independence. In fact, we aren't going to use the object-oriented approach.

"Why not use the object-oriented approach? It works so well for Perl, why not for PHP?" For one thing, in the words of Zeev Suraski, one of the designers of PHP, "PHP is not an OO language." That doesn't mean that PHP doesn't have object-oriented features ”in fact, PHP does have most of the major object-oriented constructs, but it does mean that the OO solution will not be as fast or elegant.

What other choices are there? There's really only one viable choice, and that is the function-oriented approach to creating a database-independent API with PHP. In the function-oriented approach, we have a set of generic "wrapper" functions, which are substitutes for the database-specific function calls. For example, instead of mysql_connect() , we have db_connect() .

Consider the following example of connecting to a database and fetching a row by using the function-oriented API:

 <?php include_once("DB/mysql.php"); $dbh = db_connect(array($host,$user,$pass)); if (!$dbh){     die("Cannot connect to database"); } db_select_db(array("sampleDB")); $sth = db_query("SELECT * FROM sampleTable", $dbh); if (!$sth) {     die("Cannot execute query"); } while ($row = db_fetch_row(array($sth))) {     echo $row["firstname"];     echo $row["lastname"]; } db_free_result(array($sth)); db_close(array($dbh)); ?> 

That's it ”no complex interfaces. At the top of the script, you include the file that contains the appropriate wrapper functions, and you use those generic functions instead of the database-specific functions.

In this chapter, we discuss how to implement this database-independent API by using PHP. In the next chapter, we talk about how to use the API, as well as related concepts.