Database Techniques and PHP

Picking up where we left off last week in our discussion of databases and PHP, we’ll talk about connecting, issuing a query, and more. 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.

 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 option value can either be Boolean, indicating whether or not the connection is to be persistent, or an array of options settings. The option 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’));

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.

{mospagebreak title=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());
  }

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.

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 none to begin with—an empty result), or DB_ERROR if an error occurred. The mode  parameter con trols 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
  }

Storing the row

The fetchInto() method not only gets the next row, but also 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
  }

{mospagebreak title=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) "Foundation"
    [1]=>
    string(4) "1951"
    [2]=>
    string(12) "Isaac Asimov"
  }

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) "Foundation"
    ["pub_year"]=>
    string(4) "1951"
    ["name"]=>
    string(12) "Isaac Asimov"
 
}

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) "Foundation"
    ["pub_year"]=>
    string(4) "1951"
    ["name"]=>
   
string(12) "Isaac Asimov"
  }

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

  echo "{$row->title} was published in {$row->pub_year} and was written by {$row-> name}";
 
Foundation was published in 1951 and was written by Isaac Asimov

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.

Disconnecting

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

  $db->disconnect();

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

{mospagebreak title=Advanced Database Techniques}

PEAR DB goes beyond the database primitives shown earlier; it provides several shortcut functions for fetching result rows, as well as a unique row ID system and separate prepare/execute steps that can improve the performance of repeated queries.

Placeholders

Just as printf() builds a string by inserting values into a template, the PEAR DB can build a query by inserting values into a template. Pass the query() function SQL with ? in place of specific values, and add a second parameter consisting of the array of values to insert into the SQL:

  $result = $db->query(SQL, values);

For example, this code inserts three entries into the books table:

  $movies = array(array(‘Foundation’, 1951),
                 
array(‘Second Foundation’, 1953),
                 
array(‘Foundation and Empire’, 1952));
  foreach ($books as $book) {
   
$db->query(‘INSERT INTO books (title,pub_year) VALUES (?,?)’, $book) ;
  }

There are three characters that you can use as placeholder values in an SQL query:

?

A string or number, which will be quoted if necessary (recommended)

|

A string or number, which will never be quoted

&

A filename, the contents of which will be included in the statement (e.g., for storing an image file in a BLOB field)

Prepare/Execute

When issuing the same query repeatedly, it can be more efficient to compile the query once and then execute it multiple times using the prepare(), execute(), and executeMultiple() methods.

The first step is to call prepare() on the query:

  $compiled = $db->prepare(SQL);

This returns a compiled query object. The execute() method fills in any placehold ers in the query and sends it to the RDBMS:

  $response = $db->execute(compiled, values);

The value s  array contains the values for the placeholders in the query. The return value is either a query response object, or DB_ERROR if an error occurred.

For example, we could insert multiple values into the books table like this:

  $books = array(array(‘Foundation’, 1951), 
                  array(‘Second Foundation’, 1953),
                  array(‘Foundation and Empire’, 1952));
  $compiled = $q->prepare(‘INSERT INTO books (title,pub_year) VALUES (?,?)’);
  foreach ($books as $book) {
    $db->execute($compiled, $book);
  }

The executeMultiple() method takes a two-dimensional array of values to insert:

  $responses = $db->executeMultiple(compiled, values);

The values  array must be numerically indexed from 0 and have values that are arrays of values to insert. The compiled query is executed once for every entry in values , and the query responses are collected in $responses .

A better way to write the book-insertions code is:

  $books = array(array(‘Foundation’, 1951),
                  array(‘Second Foundation’, 1953),
                  array(‘Foundation and Empire’, 1952));
 
$compiled = $q->prepare(‘INSERT INTO books (title,pub_year) VALUES (?,?)’);
  $db->insertMultiple($compiled, $books);

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

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

chat