 |  |
| |
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( )).
<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.
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.
|
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.
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.
|
Option |
Controls |
|---|---|
|
persistent |
Connection persists between accesses |
|
What to optimize for |
|
|
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'));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.
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( ));
}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.
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
}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
}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 1962A 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.
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.
|  | |
| 8.2. Relational Databases and SQL |  | 8.4. Advanced Database Techniques |
Copyright © 2003 O'Reilly & Associates. All rights reserved.