Home arrow PHP arrow Accessing Databases with DB

Accessing Databases with DB

This chapter discusses the database abstraction layer PEAR DB. This package supplies a standard set of functions for talking to many different kinds of databases (from Essential PHP Modules, Extensions, Tools, by David Sklar, 2004, Apress, ISBN: 1590592808).

  1. Accessing Databases with DB
  2. Introducing DSNs
  3. Understanding Quoting and Placeholders
  4. Examining Data Retrieval Convenience Methods
  5. Understanding Query Information
  6. Running a Query Multiple Times
  7. Introducing Sequences
By: David Sklar
Rating: starstarstarstarstar / 14
November 30, 2004

print this article



phpPEAR DB provides a consistent set of methods for using a relational database no matter what database you’re using. It supports the following PHP database extensions: FrontBase, Informix, InterBase, Microsoft SQL Server, mSQL, ODBC, MySQL, Sybase, OCI8, and PostgreSQL. A driver that supports new features in version 4 of MySQL is also in the works. This chapter covers DB version 1.4.

Exploring a Simple DB Example

Retrieving a result and displaying it in a table with DB looks like this:

// Load the DB code
require 'DB.php';

// Connect to the database
$dbh = DB::connect('mysql://user:password@host/database');

// Send a SELECT query to the database
$sth = $dbh->query('SELECT flavor, price, calories FROM ice_cream');

// Check if any rows were returned
if ($sth->numRows()) {
     print "<table>"; 
     print "<tr><th>Ice Cream Flavor</th><th>Price per Serving</th><th>Calories per Serving</th></tr>";
     // Retrieve each row 
     while ($row = $sth->fetchRow()) {
           // And print out the elements in the row
           print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2</td></tr>\n";
     print "</table>";
} else {
     print "No results";

DB::connect() is a static class method that returns an object. You interact with the database by calling methods on this object. In this example, the object is assigned to the variable $dbh, which stands for database handle. The argument to DB::connect() is a Data Source Name (DSN). DSNs are explained in the next section.

Next, you send a query to the database server with the $dbh->query() method. This method returns a statement handle that is assigned to $sth. This variable is a DB_Result object with methods that return information about the retrieved rows. The $sth->numRows() method returns the number of retrieved rows. In this example, it’s used to determine whether to print the retrieved data or just a message saying that no data was retrieved. The “Query Information” section explains methods such as numRows() that provide information about a query.

The $sth->fetchRow() method returns an array that contains one row of data retrieved from the database. The statement handle maintains an internal counter of what the “next” row to return is, so the first time you call fetchRow(), you get the first row retrieved from the database; the second time you call fetchRow(), you get the second row, and so on. After all retrieved rows have been returned, fetchRow() returns NULL instead of a result array. This makes it easy to use in a while() loop as the example does.

The fetchRow() method returns an indexed array with retrieved data. The first field requested by the SELECT query is in element 0 of the array, the second field in element 1, and so on. In this example, $row[0] is the value of the flavor column in each row, $row[1] is the price column, and $row[2] is the calories column. You can also retrieve rows as associative arrays or objects. The “Sending Queries and Retrieving Results” section discusses fetchRow() in more detail.

This chapter is from Essential PHP Modules, Extensions, Tools, by David Sklar (Apress, 2004, ISBN: 1590592808). Check it out at your favorite bookstore today.

Buy this book now.

>>> More PHP Articles          >>> More By David Sklar

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: