8.1 Using PHP to Access a Database

There are two ways to access databases from PHP. One is to use a database-specific extension; the other is to use the database-independent PEAR DB library. There are advantages and disadvantages to each approach.

If you use a database-specific extension, your code is intimately tied to the database you're using. The MySQL extension's function names, parameters, error handling, and so on are completely different from those of the other database extensions. If you want to move your database from MySQL to PostgreSQL, it will involve significant changes to your code. The PEAR DB, on the other hand, hides the database-specific functions from you; moving between database systems can be as simple as changing one line of your program.

The portability of an abstraction layer like PEAR's DB library comes at a price. Features that are specific to a particular database (for example, finding the value of an automatically assigned unique row identifier) are unavailable. Code that uses the PEAR DB is also typically a little slower than code that uses a database-specific extension.

Keep in mind that an abstraction layer like PEAR DB does absolutely nothing when it comes to making sure your actual SQL queries are portable. If your application uses any sort of nongeneric SQL, you'll have to do significant work to convert your queries from one database to another. For large applications, you should consider writing a functional abstraction layer; that is, for each database your application needs to support, write a set of functions that perform various database actions, such as get_user_record( ), insert_user_record( ), and whatever else you need, then have a configuration option that sets the type of database to which your application is connected. This approach lets you use all the intricacies of each database you choose to support without the performance penalty and limitations of an abstraction layer.

For simple applications, we prefer the PEAR DB to the database-specific extensions, not just for portability but also for ease of use. The speed and feature costs are rarely significant enough to force us into using the database-specific extensions. For the most part, the rest of this chapter gives sample code using the PEAR DB abstraction objects.

For most databases, you'll need to recompile PHP with the appropriate database drivers built into it. This is necessary whether or not you use the PEAR DB library. The help information for the configure command in the PHP source distribution gives information on how to build PHP with support for various databases. For example:

--with-mysql[=DIR]      Include MySQL support. DIR is the MySQL base                         directory. If unspecified, the bundled MySQL                         library will be used. --with-oci8[=DIR]       Include Oracle-oci8 support. Default DIR is                         ORACLE_HOME. --with-ibm-db2[=DIR]    Include IBM DB2 support.  DIR is the DB2 base                         install directory, defaults to                         /home/db2inst1/sqllib --with-pgsql[=DIR]      Include PostgreSQL support.  DIR is the PostgreSQL                         base install directory, defaults to                          /usr/local/pgsql.

You can't build PHP with support for a database whose client libraries you don't have on your system. For example, if you don't have the Oracle client libraries, you can't build PHP with support for Oracle databases.

Use the phpinfo( ) function to check for database support in your installation of PHP. For instance, if you see a section in the configuration report for MySQL, you know you have MySQL support.