Page 239 - Open Soource Technologies 304.indd

Unit 10: Database

Introduction Notes

A database is simply an organized data. A database contains tables which are basically
descriptions of type of data. Tables in turn contain records which is the actual data.

By using a common identifier between tables, it is possible to “relate” one table to another. For
instance, imagine you had a table that contained data about items sold in a store today. You
also have tables that give you the details about the items and the vendors that sell them to you.
If you want to get a list of how much of each vendors items you are sold today you will have
to relate the sales table, the items table and the vendors table by a common field they all share.
This aspect of relating data is what gives a relational database its power.
PHP has support for over 20 databases, including the most popular commercial and open
source varieties. Relational database systems such as MySQL, PostgreSQL, and Oracle are the
backbones of most modern dynamic web sites. These sites are stored shopping-cart information,
purchase histories, product reviews, user information, credit-card numbers, and sometimes even
web pages themselves.

10.1 Using PHP to Access a Database

There are two ways to access databases from PHP. One is to use a database-specific extension;
and the other is to use the database-independent PEAR DB library. There are advantages and
disadvantages to each approach.
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.
It 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 will 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 most databases, you will 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:

LOVELY PROFESSIONAL UNIVERSITY 233