Accessing Databases with ADODB
(Page 1 of 12 )
This chapter describes the behavior of ADODB version 4.03. A complete ADODB example is examined, illustrating some fundamentals of connecting to a database, sending a query, and retrieving results. Other topics covered include integer sequences, caching generating HTML, and four ways to handle errors in ADODB. (From the book
Essential PHP Tools, by David Sklar, Apress, 2004, ISBN: 1590592808.)
ADODB is a database wrapper library that simplifies many database-related tasks in PHP. The ADODB API is based on Microsoft ADO, a data access library used in Visual Basic and other Microsoft products. Although ADODB provides query helper functions such as PEAR DB, it also has functions that automate more complicated database tasks, such as turning a set of database rows into an HTML <select> menu or automatically paginating results. PEAR DB has a more rigorously defined object-oriented structure than ADODB. Because it’s a core part of the PEAR library, it integrates better with other PEAR modules than ADODB does. However, ADODB offers more advanced functionality than PEAR DB, especially with regard to HTML generation. Choosing ADODB over PEAR DB gives you more features but at the loss of some flexibility.
This chapter describes the behavior of ADODB version 4.03. You can download ADODB from http://php.weblogs.com/ADODB. The databases supported by this version of ADODB are Access, ADO, DB2, FrontBase, Informix, InterBase, Microsoft SQL Server, MySQL, Oracle, ODBC, PostgreSQL, SAPDB, SQLAnywhere, SQLite, Sybase, and Visual FoxPro.
Connecting and Simple Queries This section dissects a complete ADODB example to explain some fundamentals of connecting to a database, sending a query, and retrieving results. The section “Introducing Record Sets” discusses manipulating data returned from a query.
An ADODB Example Retrieving a result and displaying it in a table with ADODB looks like this:
// Load the ADODB code
require 'adodb/adodb.inc.php';
// Connect to the database
$conn = &ADONewConnection('mysql');
$conn->connect('localhost','phpgems','phpgems1','phpgems');
// Send a SELECT query to the database
$rs = $conn->execute('SELECT flavor, price, calories FROM _
ice_cream');
// Check if any rows were returned
if ($rs->RecordCount() > 0) {
print "<table>";
print "<tr><TH>Ice Cream Flavor</TH><TH>Price per Serving</TH><TH>Calories per Serving</TH></TR>";
// Retrieve each row
while (! $rs->EOF) {
print "<tr><TD>{$rs->fields[0]}</TD><TD>{$rs->fields[1]}</TD><TD>{$rs->f ields[2]}</TD></TR>\n";
$rs->MoveNext();
}
print "</table>";
} else {
print "No results";
}
The connection to the database is established with the ADONewConnection() function call and the Connect() method call on the returned connection handle. ADONewConnection() takes one parameter—the type of database to which to connect. It returns a connection handle object that is not yet connected to a database server. The Connect() method establishes the actual database connection. The parameters to Connect() are the host to which to connect, the username and password to use for the connection, and the database name to which to connect. After the connection is made, the Execute() method sends a query to the database. The return value of Execute() is a record set. A record set is an object that lets you access the rows returned from a SELECT query. This example uses a few properties and methods of the record set to retrieve each row from the query results. The record set holds the rows in the order in which they were returned from the database. The RecordCount() method returns the number of rows inside the record set. You use the value returned from RecordCount() to make sure there are some rows to print before executing the code to print them.
Inside the record set is a pointer to one of the rows. This pointer starts out at the first row returned from the database. The MoveNext() method advances the pointer to the next row in the record set. The EOF property of a record set is true when the internal pointer has advanced past all of the rows in the record set. EOF and MoveNext() provide the way you loop through the entire record set in the example. As long as EOF isn’t true, the internal pointer is pointing to a valid row, so you print data from the row and use MoveNext() to advance to the next row. If MoveNext() moves the internal pointer past the last row in the record set, then EOF is true and the while() loop ends.
The data from the row that the internal pointer is pointing to is accessible through the fields property of the record set. This is an array whose first element (index 0) is the first field in the row, whose second element (index 1) is the second field, and so on. You need to surround each array element with curly braces in the example to tell PHP how to interpolate the values properly in the double-quoted string.
 This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now. |
Next: Connecting, Queries and Quoting >>
More Database Articles
More By Apress Publishing
/ 37 
