PEAR DB Basics - PHP

If you're interested in learning how to access databases from PHP, you've come to the right place. This four-part series will focus on the PEAR DB system. This article is excerpted from chapter eight of the book Programming PHP, Second Edition, written by Kevin Tatroe, Rasmus Lerdorf, and Peter MacIntyre (O'Reilly, 2006; ISBN: 0596006810). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:

By: O'Reilly Media

Poor Best

Rating:

/ 8

June 14, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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