PEAR DB Basics (Programming PHP)

8.3. PEAR DB Basics

Example 8-1 is a program to build an HTML table of information about James Bond movies. 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. 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 movie information

<html><head><title>Bond Movies</title></head>
<body>
  
<table border=1>
<tr><th>Movie</th><th>Year</th><th>Actor</th></tr>
<?php
 // connect
 require_once('DB.php');
 $db = DB::connect("mysql://bondview:007@localhost/webdb");
 if (DB::iserror($db)) {
   die($db->getMessage( ));
 }
  
 // issue the query
 $sql = "SELECT movies.title,movies.year,actors.name
         FROM movies,actors
         WHERE movies.actor=actors.id
         ORDER BY movies.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 movie page

8.3.1. 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 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
Pgsql	PostgreSQL
Ibase	InterBase
Msql	Mini SQL
Mssql	Microsoft SQL Server
oci8	Oracle 7/8/8i
Odbc	ODBC
Sybase	SyBase
Ifx	Informix
Fbsql	FrontBase

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:///webdb
mysql://localhost/webdb
mysql://bondview@localhost/webdb
mysql://bondview@tcp+localhost/webdb
mysql://bondview:007@localhost/webdb

In Example 8-1, we connected to the MySQL database webdb with the username bondview and password 007.

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.

8.3.2. Connecting

Once you have a DSN, create a connection to the database using the connect( ) method. This returns a database object you'll use for tasks such as issuing queries and quoting parameters:

$db = DB::connect(DSN [, options ]);

The options value can either be Boolean, indicating whether or not the connection is to be persistent, or an array of options settings. The options values are given in Table 8-2.

Table 8-2. Connection options

Option	Controls
persistent	Connection persists between accesses
optimize	What to optimize for
debug	Display debugging information

By default, the connection is not persistent and no debugging information is displayed. Permitted values for optimize are 'performance' and 'portability'. The default is 'performance'. Here's how to enable debugging and optimize for portability:

$db = DB::connect($dsn, array('debug' => 1, 'optimize' => 'portability'));

8.3.3. Error Checking

PEAR DB methods return DB_ERROR if an error occurs. You can check for this with DB::isError( ):

$db = DB::connect($datasource);
if (DB::isError($db)) {
  die($db->getMessage( ));
}

The DB::isError( ) method returns true if an error occurred while working with the database object. If there was an error, the usual behavior is to stop the program and display the error message reported by the getMessage( ) method. You can call getMessage( ) on any PEAR DB object.

8.3.4. Issuing a Query

The query( ) method on a database object sends SQL to the database:

$result = $db->query(sql);

A SQL statement that doesn't query the database (e.g., INSERT, UPDATE, DELETE) returns the DB_OK constant to indicate success. SQL that performs a query (e.g., SELECT) returns an object that you can use to access the results.

You can check for success with DB::isError( ):

$q = $db->query($sql);
if (DB::iserror($q)) {
  die($q->getMessage( ));
}

8.3.5. Fetching Results from a Query

PEAR DB provides two methods for fetching data from a query result object. One returns an array corresponding to the next row, and the other stores the row array into a variable passed as a parameter.

8.3.5.1. Returning the row

The fetchRow( ) method on a query result returns an array of the next row of results:

$row = $result->fetchRow([ mode ]);

This returns either an array of data, NULL if there is no more data, or DB_ERROR if an error occurred. The mode parameter controls the format of the array returned, which is discussed later.

This common idiom uses the fetchRow( ) method to process a result, one row at a time, as follows:

while ($row = $result->fetchRow( )) {
  if (DB::isError($row)) {
    die($row->getMessage( ));
  }
  // do something with the row
}

8.3.5.2. Storing the row

The fetchInto( ) method also gets the next row, but stores it into the array variable passed as a parameter:

$success = $result->fetchInto(array, [mode]);

Like fetchRow( ), fetchInto( ) returns NULL if there is no more data, or DB_ERROR if an error occurs.

The idiom to process all results looks like this with fetchInto( ):

while ($success = $result->fetchInto($row)) {
  if (DB::isError($success)) {
    die($success->getMessage( ));
  }
  // do something with the row
}

8.3.5.3. Inside a row array

Just what are these rows that are being returned? By default, they're indexed arrays, where the positions in the array correspond to the order of the columns in the returned result. For example:

$row = $result->fetchRow( );
if (DB::isError($row)) {
  die($row->getMessage( ));
}
var_dump($row);
array(3) {
  [0]=>
  string(5) "Dr No"
  [1]=>
  string(4) "1962"
  [2]=>
  string(12) "Sean Connery"
}

You can pass a mode parameter to fetchRow( ) or fetchInto( ) to control the format of the row array. The default behavior, shown previously, is specified with DB_FETCHMODE_ORDERED.

The fetch mode DB_FETCHMODE_ASSOC creates an array whose keys are the column names and whose values are the values from those columns:

$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
if (DB::isError($row)) {
  die($row->getMessage( ));
}
var_dump($row);
array(3) {
  ["title"]=>
  string(5) "Dr No"
  ["year"]=>
  string(4) "1962"
  ["name"]=>
  string(12) "Sean Connery"
}

The DB_FETCHMODE_OBJECT mode turns the row into an object, with a property for each column in the result row:

$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
if (DB::isError($row)) {
  die($row->getMessage( ));
}
var_dump($row);
object(stdClass)(3) {
  ["title"]=>
  string(5) "Dr No"
  ["year"]=>
  string(4) "1962"
  ["name"]=>
  string(12) "Sean Connery"
}

To access data in the object, use the $object->property notation:

echo "{$row->title} was made in {$row->year}";
Dr No was made in 1962

8.3.5.4. Finishing the result

A query result object typically holds all the rows returned by the query. This may consume a lot of memory. To return the memory consumed by the result of a query to the operating system, use the free( ) method:

$result->free( );

This is not strictly necessary, as free( ) is automatically called on all queries when the PHP script ends.

8.3.6. Disconnecting

To force PHP to disconnect from the database, use the disconnect( ) method on the database object:

$db->disconnect( );

This is not strictly necessary, however, as all database connections are disconnected when the PHP script ends.