Page 249 - Open Soource Technologies 304.indd

Unit 10: Database

This will only work if: Notes
DB.php is in the same directory as EmployeeReport.php. This is not likely as DB.php itself
includes files, which would also have to be in the same directory.
The include_path directive in php.ini includes a path to the pear folder containing DB.php.
Next, we connect to the database:
@$DB = DB::connect (‘mysqli://root:pwdpwd@localhost/Northwind’);
This line of code will create a connection object if the connection is successful or an error object
if it is not. The: syntax will be covered when we discuss object-oriented PHP programming,
but the crux of it is that the connect() method is a class-level method rather than an object-level
method, so it can be called without first instantiating an object.
We then use the class-level isError() method to check if $DB is an error object, which would
mean that the connection failed. If it did fail, we output an error.
if (DB::isError($DB))
{
echo ‘Cannot connect to database: ‘ . $DB->getMessage();
}
If the connection succeeded, we run our query:
$Query = ‘SELECT * FROM Employees’;
$Result = $DB->query($Query);
$NumResults = $Result->numRows();
And, after writing out our header row, we loop through the query results outputting a row for
each record returned:
while ($Row = $Result->fetchRow(DB_FETCHMODE_ASSOC))
{
echo ‘<tr>’;
echo ‘<td>’ . $Row[‘FirstName’] . ‘</td>’;
echo ‘<td>’ . $Row[‘LastName’] . ‘</td>’;
echo ‘<td>’ . $Row[‘Title’] . ‘</td>’;
echo ‘<td>’ . $Row[‘Email’] . ‘</td>’;
echo ‘<td align=”right”>x’ . $Row[‘Extension’] . ‘</td>’;
}
The fetchRow() method can take one of several constants to specify how a row is returned. In
this example, we use DB_FETCHMODE_ASSOC to get the row as an associative array. Other
options are DB_FETCHMODE_ORDERED (the default) and DB_FETCHMODE_OBJECT, which
get the row as an indexed array and an object, respectively.
10.4 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.
10.4.1 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

LOVELY PROFESSIONAL UNIVERSITY 243