PEAR DB Basics

Example 8-1 is a program to build an HTML table of information about science fiction books. 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. Be sure to verify how to turn on PEAR with your setup of PHP, as the Unix/Linux flavor is slightly different to that of Windows. Go to http://www.pear.php.net/manual/en/installation.getting.php for a starting point on installation. 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 book information
<html><head><title>Library Books</title></head>
<body>

<table border=1>
<tr><th>Book</th><th>Year Published</th><th>Author</th></tr>
<?php
 // connect
 require_once('DB.php');
 $db = DB::connect("mysql://librarian:passw0rd@localhost/library");
 if (DB::iserror($db)) {
   die($db->getMessage(  ));
 }

 // issue the query
 $sql = "SELECT books.title,books.pub_year,authors.name
         FROM books, authors
         WHERE books.authorid=authors.authorid
         ORDER BY books.pub_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 Library page

Data Source Names

A data source name (DSN ) is ...

Get Programming PHP, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial