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 NamesA 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.
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 ConnectingOnce 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.
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 CheckingPEAR 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 QueryThe 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 QueryPEAR 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 rowThe 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 rowThe 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 arrayJust 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 resultA 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 DisconnectingTo 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. |
EAN: 2147483647
Pages: 168