Databases and PHP

If you’re interested in learning how to access databases from PHP, you’ve come to the right place. This four-part series will focus on the PEAR DB system. This article is excerpted from chapter eight of the book Programming PHP, Second Edition, written by Kevin Tatroe, Rasmus Lerdorf, and Peter MacIntyre (O’Reilly, 2006; ISBN: 0596006810). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

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 backbone of most modern dynamic web sites. In these are stored shopping-cart information, purchase histories, product reviews, user information, credit-card numbers, and sometimes even web pages themselves.

This chapter covers how to access databases from PHP. We focus on the PEAR DB system, which lets you use the same functions to access any database, rather than on the myriad database-specific extensions. In this chapter, you’ll learn how to fetch data from the database, how to store data in the database, and how to handle errors. We finish with a sample application that shows how to put various database techniques into action.

This book cannot go into all the details of creating web database applications with PHP. For a more in-depth look at the PHP/MySQL combination, see Web Database Applications with PHP and MySQL by Hugh Williams and David Lane (O’Reilly).

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 however. 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. This would, however, take quite some time to build from scratch.

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.

New in PHP Version 5 is the compact and small database connection called SQLite. As its name suggests, it is a small and light weight database tool. This database product comes with PHP 5 and has replaced the default database tool that once was MySQL. You can still use MySQL with PHP, but you have to do a little work to get it set up. SQLite is ready to go right “out of the box” when you install PHP, so if you are looking for a light weight and compact database tool, then be sure to read up on SQLite.

{mospagebreak title=Relational Databases and SQL}

A Relational Database Management System (RDBMS) is a server that manages data for you. The data is structured into tables, where each table has a number of columns, each of which has a name and a type. For example, to keep track of science fiction books, we might have a “books” table that records the title (a string), year of release (a number), and the author.

Tables are grouped together into databases, so a science fiction book database might have tables for time periods, authors, and villains. An RDBMS usually has its own user system, which controls access rights for databases (e.g., “user Fred can update database authors”).

PHP communicates with relational databases such as MySQL and Oracle using the Structured Query Language (SQL). You can use SQL to create, modify, and query relational databases.

The syntax for SQL is divided into two parts. The first, Data Manipulation Language, or DML, is used to retrieve and modify data in an existing database. DML is remarkably compact, consisting of only four verbs: SELECT , INSERT , UPDATE , and DELETE . The set of SQL commands used to create and modify the database structures that hold the data is known as Data Definition Language, or DDL. The syntax for DDL is not as standardized as that for DML, but as PHP just sends any SQL commands you give it to the database, you can use any SQL commands your database supports.

The SQL Command file for creating this sample library database is available in a file called library.sql.

Assuming you have a table called books , this SQL statement would insert a new row (check web site companion for source files):

  INSERT INTO books VALUES (4, ‘I, Robot’, ‘0-553-29438-5′, 1950)

This SQL statement inserts a new row but specifies the columns for which there are values:

  INSERT INTO books (authorid, title, ISBN, pub_year) VALUES (4, ‘I, Robot, ‘0-553-29438-5′, 1950)

To delete all books that were published in 1979 (if any), we could use this SQL statement:

  DELETE FROM books WHERE pub_date = 1979

To change the year for Roots to 1983, use this SQL statement:

  UPDATE books SET pub_year=1983 WHERE title=’Roots’

To fetch only the books published in the 1980s, use:

  SELECT * FROM books WHERE pub_year > 1979 AND pub_year < 1990

You can also specify the fields you want returned. For example:

  SELECT title, pub_year FROM books WHERE pub_year > 19790 AND pub_year < 1990

You can issue queries that bring together information from multiple tables. For example, this query joins together the book and author tables to let us see who wrote each book:

  SELECT authors.name, books.title FROM books, authors
 
WHERE authors.authorid = books.authorid.

For more on SQL, see SQL in a Nutshell, by Kevin Kline (O’Reilly).

{mospagebreak title=PEAR DB Basics}

Example 8-1 is a program to build an HTML table of information about science fiction books. It demonstrates how to use the PEAR DB library (which comes with PHP) to connect to a database, issue queries, check for errors, and transform the results of queries into HTML. Be sure to verify how to turn on PEAR with your setup of PHP, as the Unix/Linux flavor is slightly different to that of Windows. Go to http:// www.pear.php.net/manual/en/installation.getting.php for a starting point on installation. The library is object-oriented, with a mixture of class methods (DB::connect(), DB::iserror()) and object methods ( $db->query() , $q->fetchInto() ).

Example 8-1.  Display book information

<html><head><title>Library Books</title></head>
<body>

<table border=1 >
<tr><th>Book</th><th>Year Published</th><th>Author</th></tr>
<?php
 // connect
 
require_once(‘DB.php’);
 
$db = DB::connect("mysql://librarian:passw0rd@localhost/ library");
 
if (DB::iserror($db)) {
  
die($db->getMessage());
 }

 // issue the query
 
$sql = "SELECT books.title,books.pub_year,authors.name
        
FROM books, authors
        
WHERE books.authorid=authors.authorid
        
ORDER BY books.pub_year ASC";
 $q = $db->query($sql);
 if (DB::iserror($q)) {
   die($q->getMessage());
 }

 // generate the table
 
while ($q->fetchInto($row)) {
?>
<tr><td><?= $row[0] ?></td>
    <td><?= $row[1] ?></td>
   
<td><?= $row[2] ?></td>
</tr>
<?php
 
}
?>

The output of Example 8-1 is shown in Figure 8-1.


Figure 8-1.  The Library page

{mospagebreak title=Data Source Names}

A data source name (DSN) is a string that specifies where the database is located, what kind of database it is, the username and password to use when connecting to the database, and more. The components of a DSN in PEAR are assembled into a URL-like string:

  type(dbsyntax)://username:password@protocol+hostspec/ database

The only mandatory field is type , which specifies the PHP database backend to use. Table 8-1 lists the implemented database types at the time of writing.

Table 8-1. PHP database types

Name Database
Mysql MySQL
mysqli MySQL (for MySQL >= 4.1)
Pgsql PostgreSQL
Ibase InterBase
Msql Mini SQL
Mssql Microsoft SQL Server
oci8 Oracle 7/8/8i
Odbc ODBC
Sybase SyBase
Ifx Informix
Fbsql FrontBase
Dbase DBase
Sqlite SQLite

The protocol  is the communication protocol to use. The two common values are "tcp" and "unix , " corresponding to Internet and Unix domain sockets. Not every database backend supports every communications protocol.

These are some sample valid data source names:

  mysql:///library
  mysql://localhost/library
  mysql://librarian@localhost/library
  mysql://librarian@tcp+localhost/library
  mysql:// librarian:passw0rd@localhost/library

In Example 8-1, we connected to the MySQL database library with the username librarian and password passw0rd .

A common development technique is to store the DSN in a PHP file and include that file in every page that requires database connectivity. Doing this means that if the information changes, you don’t have to change every page. In a more sophisticated settings file, you might even switch DSNs based on whether the application is running in development or deployment mode.

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]

chat