home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Programming PHPProgramming PHPSearch this book

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

Figure 8-1. The movie page

8.3.1. Data Source Names

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.

Table 8-1. PHP database types

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.

8.3.5. 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.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.