Database Abstraction With PHP - Different Strokes (
Page 5 of 11 )
Let's now look at a few of the other
methods exposed by the PEAR abstraction layer. Consider the following variant of
the example you just saw, this one retrieving the resultset as an associative,
or string-indexed, array:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the DB abstraction layer
include("DB.php");
// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");
// execute query
$query = "SELECT * FROM cds";
$result = $dbh->query($query);
// iterate through rows and print column data
// in the form TITLE - ARTIST
// in this case, each row is fetched as an associative array,
// whose keys are the column names
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
echo $row['title'] . " - " . $row['artist'] . "\n";
}
// get and print number of rows in resultset
echo "\n[" . $result->numRows() . " rows returned]\n";
// close database connection
$dbh->disconnect();
?>
In this case, the additional argument to the fetchRow()
function requests that the data be retrieved as elements of an associative array
(the default is a "regular" array, indexed by number rather than string). The
keys of this array are the column names of the resultset. In the event that a
column name is repeated - as might happen, for example, with a table join - the
last one will be used.
It's also possible to retrieve a row as an object,
with the fields within it exposed as object properties. Take a look:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the DB abstraction layer
include("DB.php");
// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");
// execute query
$query = "SELECT * FROM cds";
$result = $dbh->query($query);
// iterate through rows and print column data
// in the form TITLE - ARTIST
// in this case, each row is fetched as an object,
// whose properties correspond to the column names
while($row = $result->fetchRow(DB_FETCHMODE_OBJECT))
{
echo $row->title . " - " . $row->artist . "\n";
}
// get and print number of rows in resultset
echo "\n[" . $result->numRows() . " rows returned]\n";
// close database connection
$dbh->disconnect();
?>
You can play the tyrannical dictator and impose a default
mode for resultset retrieval with the setFetchMode() method. The following
example demonstrates:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the DB abstraction layer
include("DB.php");
// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");
// set default mode for all resultsets
$dbh->setFetchMode(DB_FETCHMODE_ASSOC);
// execute query
$query = "SELECT * FROM cds";
$result = $dbh->query($query);
// iterate through rows and print column data
// in the form TITLE - ARTIST
while($row = $result->fetchRow())
{
echo $row['title'] . " - " . $row['artist'] . "\n";
}
// get and print number of rows in resultset
echo "\n[" . $result->numRows() . " rows returned]\n";
// close database connection
$dbh->disconnect();
?>
In this case, the default mode of operation for the
fetchRow() method is to structure each row into an associative array. Isn't it
wonderful how PHP lets you be both lazy and efficient at the same
time?
/ 57 
