Accessing Databases with ADODB

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

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

{mospagebreak title=Connecting, Queries and Quoting}

The Connect() method calls the appropriate underlying PHP functions to connect to the appropriate database. ADODB also has a PConnect() method to establish a persistent connection to the database. This is supported for Oracle, MySQL, PostgreSQL, Microsoft SQL Server, and ODBC. The tests/testdatabases.inc.php file in the ADODB distribution includes lots of example syntax for Connect() and PConnect() with the different kinds of databases that ADODB supports. Table 2-1 lists the driver strings that can be passed to ADONewConnection() and the databases to which they correspond. Because some databases have multiple driver string possibilities, check the online ADODB manual at http://php.weblogs.com/adodb_manual for the latest details on a particular driver string.

 

Driver String

Database

ado A generic ADO database

access

Microsoft Access

ado_access

Microsoft Access via ADO

vfp

Visual FoxPro

db2

DB2

fbsql

FrontBase

ibase

InterBase 6 or earlier

firebird

InterBase (Firebird version)

borland_ibase

InterBase 6.5 or later (Borland version)

informix72

Informix 7.2 or earlier

informix

Informix

mysql

MySQL (without transaction support)

mysqlt

MySQL (with transaction support)

maxsql Same as mysqlt
oci8 Oracle 8 and 9
oci805 Oracle 8.0.5
oci8po Oracle 8 and 9*
odbc_oracle Oracle via ODBC
odbc An ODBC DSN
postgres7 PostgreSQL 7
postgres64 PostgreSQL 6.4 and earlier
postgres Same as postgres7
sapdb SAPDB via ODBC
sqlanywhere SQLAnywhere via ODBC
sqlite SQLite (only available with PHP 5)
mssql Microsoft SQL Server 7 or later
mssqlpo Microsoft SQL Server 7 or later**
ado_mssql Microsoft SQL Server via ADO
odbc_mssql Microsft SQL Server via ODBC
sybase Sybase

Table 2-1. ADODB Driver Strings and Databases

Table notes:
* Uses ? for bind variables in Prepare() and lowercase for field names
** Converts the || concatenation operator to +

Queries and Quoting

The Execute() method is the central way in ADODB to send a query to the database server. In its simplest form, as used previously, you pass Execute() an SQL query, and it returns a record set that holds the results. You can also use placeholders with Execute(). Any question marks in your query are replaced with values from an array passed to Execute() as a second argument. The question marks are placeholders for the values that come from the array. This technique is useful if you need to run similar queries repeatedly:

$sql = ‘SELECT * FROM ice_cream WHERE flavor LIKE ?’;
// Get information about Chocolate
$rs = $conn->execute($sql,array(‘Chocolate’));
// Get information about Vanilla
$rs = $conn->execute($sql,array(‘Vanilla’));

Placeholders also make quoting easier. String values substituted for placeholders are surrounded by single quotes and have single quotes in them escaped in the appropriate manner for the database you’re using. If you’re not using placeholders, you can quote values with the qstr() method:

$flavor = $conn->qstr($_REQUEST['flavor']);
$rs = $conn->execute(“SELECT * FROM ice_cream WHERE flavor LIKE $flavor”);

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Introducing Record Sets}

The fundamental unit of data manipulation in ADODB is the record set. It is the set of rows that results from a database query that retrieves information from the database, such as a SELECT query. The record set maintains an internal pointer that starts out pointing at the first row in the record set. A record set object contains methods to move that internal pointer around as well as to retrieve a row or rows based on where the internal pointer is. Rows are usually represented as ordered arrays, but you can also tell ADODB to provide them as associative arrays. The row of data that the internal pointer points to is available in the fields property of the record set. When the internal pointer moves, the fields property changes to hold the values of the new row that is pointed to.

Moving the Internal Pointer

In the previous section, you used the MoveNext() method to move the internal pointer to the next row in the record set. The similarly named methods MoveFirst() and MoveLast() move the internal pointer to the first and last rows of the record set, respectively:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
// print last row
$rs->MoveLast();
print “Flavor {$rs->fields[0]} has {$rs->fields[1]} calories and costs
print “${$rs->fields[2]}.n”;
// print first row $rs->MoveFirst();
print “Flavor {$rs->fields[0]} has {$rs->fields[1]} calories and costs
print “${$rs->fields[2]}.n”;

To move the internal pointer of a record set to a specific row, use the Move() method and pass it the row number:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
// print second row $rs->Move(2);
print “Flavor {$rs->fields[0]} has {$rs->fields[1]} calories and costs
print “${$rs->fields[2]}.n”;

To find the position of the internal pointer, use the CurrentRow() method. It returns the current row number, starting at 0.

Only some ADODB database drivers support moving the internal pointer backward using Move() or MoveFirst(). These are the mysql driver and the postgres64 driver. The hasMoveFirst property of the ADOConnection object is true if you can move the internal pointer backward in record sets from a given connection.

Row Format

You can change the format of record set rows by setting the global variable $ADODB_FETCH_MODE. The possible values are these four constants:

  • ADODB_FETCH_DEFAULT

  • ADODB_FETCH_NUM

  • ADODB_FETCH_ASSOC

  • ADODB_FETCH_BOTH

The default value of $ADODB_FETCH_MODE is ADODB_FETCH_DEFAULT, which means the default fetch mode of the particular database driver you are using. To have record set rows be formatted as ordered numeric arrays, use ADODB_FETCH_NUM. For associative arrays, use ADODB_FETCH_ASSOC. For arrays with both numeric and string keys, use ADODB_FETCH_BOTH. The ADODB_FETCH_BOTH setting creates an array that holds all the keys and values from the numeric array that ADODB_FETCH_NUM creates as well as all the keys and values from the associative array that ADODB_FETCH_ASSOC creates.

To access a row in the record set as an object instead of an array, use the FetchObject() method. It returns an object whose properties correspond to each field in the current record set row. The property names are all uppercase. You can use FetchObject() and the object it returns instead of the fields array of the record set:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
while (! $rs->EOF) {
     $ob = $rs->FetchObject();
     print “Flavor $ob->FLAVOR has $ob->CALORIES calories and costs
     print “$$ob->PRICE.n”;
     $rs->MoveNext();
}

If you want a record set row as an object whose property names are not changed to uppercase, use FetchObj() instead. It sets the property names to what the database reports the field names are without changing their case.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Retrieving Multiple Rows}

The record set object also provides some methods to retrieve many rows at once. The GetArray() method returns an numeric array whose values are the individual rows in the record set. The rows themselves are arrays whose structure depends on the value of $ADODB_FETCH_MODE. The GetArray() method retrieves all of the rows from the position of the record set internal pointer to the end of the record set. To retrieve fewer rows, pass the number of rows you want to GetArray():

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
// Just retrieve three rows
$rows = $rs->GetArray(3);

You can also use GetRows() as a synonym for GetArray(). Similar to GetArray() is GetAssoc(), which returns an associative array instead of a numeric array. The keys of this associative array are the values of the first field of each row in the record set. For example, in this query, the keys of $rows are the different values in the flavor column of the table:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
$rows = $rs->GetAssoc();

The keys of $rows are Chocolate, Vanilla, “Heavenly” Hash, and Diet Cardboard. Although the keys in the returned associative array of GetAssoc() are different from the returned numeric array of GetArray(), the values are the same. Each value in the returned array is itself an array containing one row of the record set. Whether that array is numeric or associative depends on $ADODB_FETCH_MODE.

With $ADODB_FETCH_MODE set to ADODB_FETCH_ASSOC, the array returned previously by GetArray() looks like this, when formatted with print_r():

Array
(
     [0] => Array
          (
               [flavor] => Chocolate 
               [calories] => 10 
               [price] => 4.50 
          ) 


     [1] => Array
          ( 
               [flavor] => Vanilla 
               [calories] => 20 
               [price] => 4.50 
          ) 


     [2] => Array
          ( 
               [flavor] => “Heavenly” Hash 
               [calories] => 60 
               [price] => 5.95 
          ) 


     [3] => Array
          ( 
               [flavor] => Diet Cardboard 
               [calories] => 0 
               [price] => 1.15 
          )


)

In contrast, the array returned by GetAssoc() looks like this:

Array

     [Chocolate] => Array
          ( 
               [calories] => 10 
               [price] => 4.50 
          )

 
     [Vanilla] =>  Array
          ( 
               [calories] => 20 
               [price] => 4.50 
          ) 


     ["Heavenly" Hash] => Array
          (       
               [calories] => 60 
               [price] => 5.95 
          ) 


     [Diet Cardboard] => Array
          (               
               [calories] => 0 
               [price] => 1.15 
          )

 
)

Both GetArray() and GetAssoc() return rows starting at the current position of the internal pointer. Both return all rows from the internal pointer to the end of the record set, but you can restrict the number of rows that GetArray() returns as discussed previously.

Processing Rows

ADODB also provides a way to process all rows in a record set with its RSFilter() function, which is defined in the adodb/rsfilter.inc.php file. Provided a record set and a callback function, RSFilter() calls the callback function on each row in the record set. This example uses the built-in function money_format() to format the price of each ice cream flavor:

require ‘adodb/rsfilter.inc.php’;
setlocale(LC_MONETARY,’en_US’);
$rs = $conn->Execute(‘SELECT flavor,calories,price FROM ice_cream’);
$rs = RSFilter($rs,’fix_price’);
function fix_price(&$ar,$rs) { $ar[2] = money_format(‘%.2n’,$ar[2]); }

After retrieving a record set with Execute(), you call RSFilter(), passing it the record set and the callback name. The RSFilter() function returns the modified record set, so you have to assign its result back to $rs. The callback function, fix_price(), accepts two arguments. The first, passed by reference, is the array that holds a row of the record set. The second argument is the record set itself. The fix_price() callback doesn’t need it, but you could use properties of the record set object to determine how to format data inside your callback. Inside fix_price(), the third element of $ar, corresponding to the price field in the row, is reformatted using money_format(). Because $ar is passed by reference to fix price(), you can just assign it a new value, and you don’t need to return anything. After processing all the rows, RSFilter() moves the internal pointer to the beginning of the record set.

Accessing Rows with the Iterator Interface

In PHP 5, ADODB provides Iterator access to a record set. You can call all of the Iterator interface methods on the record set object and loop through the record set with foreach. Using foreach lets you work with each row individually:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
foreach ($rs as $index => $row) {
     printf(“Row %d is %s, calories = %d, price = $%.02fn”,
          $index, $row['flavor'], $row['calories'], $row['price']);
}

This prints the following:

Row 0 is Chocolate, calories = 10, price = $4.50
Row 1 is Vanilla, calories = 20, price = $4.50
Row 2 is “Heavenly” Hash, calories = 60, price = $5.95
Row 3 is Diet Cardboard, calories = 0, price = $1.15

Each time through the foreach loop, $index is set to the numerical row index, starting at 0, and $row is set to an array of data retrieved from the database. The format of $row respects the $ADODB_FETCH_MODE variable with regard to whether it is an associative array, a numeric array, or a combination of the two.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Understanding Error Handling}

There are four ways to handle errors in ADODB. The first is to check the return value from each ADODB function call and explicitly take an appropriate action if there is an error. If you turn on the $conn->debug flag, you can see the error messages:

$conn->debug = 1;
$rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);
if (! $rs) { print “Query Error”; }

Because the cookies table doesn’t exist, an error message is printed that includes this text:

1146: Table ‘phpgems.cookies’ doesn’t exist

Turning on $conn->debug also causes some diagnostic information about queries to be displayed, so this is not something you should do in a production environment. It is helpful for testing and debugging, however.

The second way is to use ADODB’s trigger_error() error-handling mode. In this mode, ADODB throws an error with PHP’s trigger_error() function whenever Connect(), PConnect(), or a query execution function such as Execute() fails. You don’t have to do anything special to check return values because your program exits when there is an error. To turn on this error handling mode, require the file adodb/adodb-errorhandler.inc.php. For example:

require ‘adodb/adodb-errorhandler.inc.php’;
// … establish connection to the database …
$rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);

This exits with the following error message:

Fatal error: mysql error: [1146: Table 'phpgems.cookies' doesn't exist] in EXECUTE(“SELECT flavor,calories,price FROM cookies”) in /usr/local/php/lib/adodb/adodb-errorhandler.inc.php on line 75

Because ADODB uses trigger_error() to throw these errors, you can catch them by defining your own error handler. Call set_error_handler() with the name of your error-handling function. The error-handling function can do whatever you want with the error. This example just prints the error message to the error log:

set_error_handler(‘check_errors’);
$rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);
function check_errors($errno, $error, $file, $line) {
error_log(“ADODB Error: $error”);
}

The check_errors() function writes the following message to the error log:

ADODB Error: mysql error: [1146: Table 'phpgems.cookies' doesn't exist] in EXECUTE(“SELECT flavor,calories,price FROM cookies”)

One benefit of defining your own error handler is that your program doesn’t automatically exit when ADODB throws an error. If you want it to exit, you can call exit() or die() inside your error handler, but you don’t have to do so.

The third error handling method is to use ADODB’s PEAR_Error error-handling mode. Turn on this mode by requiring the file adodb/adodb-errorpear.inc.php. In this mode, ADODB creates a PEAR_Error object when Connect(), PConnect(), or a query execution function fails. You can use the PEAR_Error objects in two ways. First, by testing the return values from the ADODB functions and using the ADODB PEAR_Error() function, you can retrieve the error object when necessary and process it:

require ‘adodb/adodb-errorpear.inc.php’;
// … establish a connection to the database …
$rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);
if (! $rs) {
    $err = ADODB_PEAR_Error();
    error_log(‘Query Error: ‘.$err->getMessage());
}

Just like you can take action automatically on errors generated by trigger_error() if you set your own error handler, you can take action automatically when PEAR errors occur by using the PEAR::setErrorHandling() method. Call PEAR::setErrorHandling() with a constant that indicates how you want errors to be handled. The PEAR ERROR_PRINT constant causes the errors to be printed. The PEAR_ERROR_DIE constant causes errors to be printed, and then the program immediately exits:

PEAR::setErrorHandling(PEAR_ERROR_DIE);
$rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);

PEAR_Error mode also supports custom error handlers. Indicate the custom error handler to be called with the PEAR_ERROR_CALLBACK constant:

PEAR::setErrorHandling(PEAR_ERROR_CALLBACK,’check_errors’); $rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);
function check_errors($err) {
    error_log(‘Query Error: ‘.$err->getMessage());
}

When an error occurs, the callback set up by PEAR::setErrorHandling() is called with a PEAR_Error object as its only argument. Inside the callback, you can print error messages, e-mail your database administrator there has been a problem, exit the program, or take any other action that the error warrants.

The fourth error handling method is with exceptions. This is only available if you are using PHP 5. Turn on this mode by requiring the file adodb/adodb-exceptions.inc.php. In this mode, ADODB throws an exception of type ADODB_Exception when an error occurs. Put your code that uses ADODB functions inside a try/catch block to handle an error:

require ‘adodb/adodb-exceptions.inc.php’;
try { 
  $conn = &ADONewConnection(‘mysql’);
  $conn->Connect(‘localhost’,'phpgems’,'phpgems1′,’phpgems’); 
  $rs = $conn->execute(‘SELECT flavor,calories,price FROM cookies’);
} catch (ADODB_Exception $e) {
          print ‘Database Error: ‘.$e->getMessage();
} catch (Exception $e) {
          print “Something else went wrong: “.$e->getMessage();
}

The first catch block in the previous code handles any exceptions generated by ADODB, and the second catch block handles any other exceptions that may have been generated. In a database context, exceptions are useful for cleaning up any unfinished business. When handling an ADODB_Exception,you can roll back a partially completed transaction or close any open connections to a database.

Introducing Sequences

ADODB provides integer sequences for you to use. You can retrieve the next value from a particular sequence by passing the sequence name to the genID() method:

$flavor_id = $conn->genID(‘flavors’);
$conn->Execute(‘INSERT INTO ice_cream (id,flavor) VALUES (?,?)’, array($flavor_id,’Walnut’));

If you call genID() without a sequence name, it retrieves the next value from the default sequence, named adodbseq. The genID() method creates a sequence if it doesn’t already exist. You can create a sequence without getting a new value from it with CreateSequence():

$conn->CreateSequence(‘flavors’);

Whether created automatically by genID() or explicitly by CreateSequence(), sequences start at 1 by default. You can start a sequence by passing a second argument to genID() or CreateSequence():

$flavor_id = $conn->genID(‘flavors’,31);
$conn->CreateSequence(‘flavors’,31);

The genID() method only uses the second argument to set the start value of the sequence if it is creating the sequence. If the sequence already exists, genID() returns the next value in the sequence.

To delete an existing sequence, use DropSequence():

$conn->DropSequence(‘flavors’);

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Generating HTML}

ADODB makes it very easy to perform common HTML generation tasks with information retrieved from a database. These tasks include displaying a record set in an HTML table, printing <select> tags containing data from a record set, and splitting data from a record set across multiple pages.

Displaying a Record Set in an HTML Table

The rs2html() function produces an HTML table of results with just one function call. The function is defined in the adodb/tohtml.inc.php file, so you must include or require that file to use rs2html(). Just pass a record set to rs2html(), and it prints an HTML table:

require ‘adodb/tohtml.inc.php’;
// Assume $conn is a valid database connection
$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
rs2html($rs);

This prints the following HTML:

<table COLS=3 BORDER=’1′ WIDTH=’98%’>

<TH>flavor</TH><TH>calories</TH><TH>price</TH>

<TR valign=top>
     <TD>Chocolate </TD>
     <TD align=right>10 </TD>
     <TD align=right>4.50 </TD>

</TR>

<TR valign=top>
     <TD>Vanilla </TD>
     <TD align=right>20 </TD>
     <TD align=right>4.50 </TD>

</TR>

<TR valign=top>
     <TD>”Heavenly” Hash </TD>
     <TD align=right>60 </TD>
     <TD align=right>5.95 </TD>

</TR>

<TR valign=top> 
     <TD>Diet Cardboard </TD> 
     <TD align=right>0 </TD>
     <TD align=right>1.15 </TD>

</TR>

</table>

Figure 2-1 shows a rendered version of the HTML table.



Figure 2-1. The rs2html() function generates an HTML table.

By default, the table produced by rs2html() has BORDER=’1′ WIDTH=’98%’ as the attributes for the <table> tag, but you can override those defaults by passing different attributes as a second argument to rs2html(). Using the record set from the previous example, rs2html($rs,’CLASS=”fancytable”‘) prints a table whose opening tag is as follows:

<table COLS=3 CLASS=”fancytable”>

You can also change the text of the table’s header row. By default, rs2html() uses the column names in the result set, but if an array of strings is passed as the third argument to rs2html(), it uses the values in that array instead. Using the record set from the previous example, rs2html($rs,false,array(‘Ice Cream Flavor’,'Calorie Count’,'Cost’)) prints a table whose header row is as follows:

<TH>Ice Cream Flavor</TH>
<TH>Calorie Count</TH><TH>Cost</TH>

By passing false as a second argument to rs2html() in this example, you tell the function to use the defaults for <table> tag attributes.

The fourth argument to rs2html() is a boolean that controls whether string values in the table have their HTML entities encoded by htmlentities(). It defaults to true. If you don’t want those values to have their entities encoded, pass false. This doesn’t affect entity encoding of the values in the table header row. When rs2html() uses the column names from the result set, it always encodes their HTML entities. When it uses column names passed in as its third argument, it never encodes their HTML entities.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=More HTML Generation}

The fifth argument to rs2html() is a boolean that controls whether the function prints data or returns data. The default, true, means that rs2html() prints an HTML table. Passing false for this argument tells rs2html() to return the HTML table as a string instead.

The behavior of rs2html() is also controlled by two global variables: $gSQLMaxRows and $gSQLBlockRows. The $gSQLMaxRows variable controls how many rows from one record set rs2html() prints. It defaults to 1000. The $gSQLBlockRows variable controls how many rows from one record set rs2html() prints in one table. It defaults to 20. If a record set contains more than $gSQLBlockRows rows, rs2html() splits the records into multiple tables. The first $gSQLBlockRows rows go in the first table, the next $gSQLBlockRows rows go into the next table, and so on. You can set $gSQLBlockRows to 2 to see this at work with the ice cream example:

$rs = $conn->execute(‘SELECT flavor,calories,price FROM ice_cream’);
$gSQLBlockRows = 2;
rs2html($rs);

The following is the HTML output. Figure 2-2 shows what it looks like in a browser.

<table COLS=3 BORDER=’1′ WIDTH=’98%’>

<TH>flavor</TH><TH>calories</TH><TH>price</TH>

<TR valign=top>
     <TD>Chocolate&nbsp;</TD>
     <TD align=right>10&nbsp;</TD>
     <TD align=right>4.50&nbsp;</TD>

</TR>  

</TR valign=top>
     <TD>Vanilla&nbsp;</TD>
     <TD align=right>20&nbsp;</TD>
     <TD align=right>4.50&nbsp;</TD>

</TR>

</table>

<table COLS=3 BORDER=’1′ WIDTH=’98%’>

<TH>flavor</TH><TH>calories</TH><TH>price</TH><TR valign=top>
     <TD>&quot;Heavenly&quot; Hash&nbsp;</TD>
     <TD align=right>60&nbsp;</TD>
     <TD align=right>5.95&nbsp;</TD>

</TR

<TR valign=top> 
     <TD>Diet Cardboard&nbsp;</TD>
     <TD align=right>0&nbsp;</TD> 
     <TD align=right>1.15&nbsp;</TD>

</TR>

</table>


Figure 2-2. The rs2html() function can generate multiple tables per page.

After putting two rows in the first table, rs2html() closes the table and starts a new one, complete with header row. The remaining two rows are put in the second table. Splitting large record sets into multiple tables can help browsers render the data quicker because a browser can’t render a table until it receives all the data in the table. It can also be helpful for users to see the header row interspersed periodically throughout a large data set so they know what each column means.

The rs2html() function prints rows from the record set starting from the current internal pointer position in the record set. If you’ve moved the internal pointer, by calling MoveNext(), for example, you need to move it back to the beginning of the record set with MoveFirst() if you want rs2html() to print the entire record set. After rs2html() runs, it leaves the internal pointer at the end of the record set, so you also need to use MoveFirst() if you want to call rs2html() twice on the same record set:

rs2html($rs);
$rs->MoveFirst();
rs2html($rs);

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Printing <select> Tags Containing Data from a Record Set}

Record set objects have two methods that return <select> tags built from the data in the record set: GetMenu() and GetMenu2(). The functions are identical except for how they handle default values (more about that in a few paragraphs). The functions use the first column in the record set as the labels for each option and the second column as the values. If you have an ID column and aname column in a table, put the name column first and the ID column second in your query:

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu(‘which_flavor’);

This prints the following <select> menu:

<select name=”which_flavor” >
<option></option>
<option value=”1″>Chocolate</option>
<option value=”2″>Vanilla</option>
<option value=”3″>”Heavenly” Hash</option>
<option value=”4″>Diet Cardboard</option>
</select>

The first argument to GetMenu() is used as the name attribute of the <select> tag. The <option> tags are printed one per line in the order that their rows appear in the result set. HTML entities, such as the quotation marks in “Heavenly” Hash, are encoded.

To have one <option> tag marked as selected so the browser treats it as the default value, pass the label for that tag as the second argument to GetMenu():

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu(‘which_flavor’,'Vanilla’);

This produces the following:

<select name=”which_flavor” >
<option></option>
<option value=”1″>Chocolate</option>
<option selected value=”2″>Vanilla</option>
<option value=”3″>”Heavenly” Hash</option>
<option value=”4″>Diet Cardboard</option>
</select>

Default value handling is where GetMenu() and GetMenu2() differ. The GetMenu2() function marks a choice as selected when its value matches the second argument passed in. To make Vanilla the default with GetMenu2(), pass 2 as a second argument:

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu2(‘which_flavor’,2);

This prints the same menu as in the previous example.

The menus that you’ve produced with GetMenu() and GetMenu2() so far all have an initial blank option. This is useful for checking that a user has selected an option in mandatory fields. If you want to turn it off, pass false as the third argument to either function:

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu2(‘which_flavor’,null,false);

This produces a menu without an initial blank option:

<select name=”which_flavor” >
<option value=”1″>Chocolate</option>
<option value=”2″>Vanilla</option>
<option value=”3″>”Heavenly” Hash</option>
<option value=”4″>Diet Cardboard</option>
</select>

By passing null as the second argument to GetMenu2(), you avoid setting any option to selected while still being able to specify that you don’t want an initial blank.

The next two arguments to GetMenu() and GetMenu2() control whether the user is allowed to select multiple options of the menu and, if so, how many options to display at once. These arguments set the multiple and size attributes of the <select> tag. To set the multiple attribute, pass true as a fourth argument. To set the size attribute, pass the value to set size to as the fifth argument. The size attribute is only relevant if the multiple attribute is set. If multiple is set, then the menu name has square brackets appended to it. This tells PHP to treat the submitted form values as an array. The following is an example:

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu(‘which_flavor’,null,false,true,3);

This produces the following menu:

<select name=”which_flavor[]” multiple size=3 >
<option value=”1″>Chocolate</option>
<option value=”2″>Vanilla</option>
<option value=”3″>”Heavenly” Hash</option>
<option value=”4″>Diet Cardboard</option>
</select>

The sixth argument to the menu functions is a string containing additional attributes for the <select> tag. You can use this argument to specify values for a style, class, or other attribute. If you want to pass additional attributes in this argument, you need to fill in defaults or appropriate values for all of the preceding arguments:

$rs = $conn->execute(‘SELECT flavor,id FROM ice_cream’);
print $rs->GetMenu(‘which_flavor’,null,false,null,null,’class=”bigselect”‘);

This adds the class=”bigselect” attribute to the <select> tag:

<select name=”which_flavor” class=”bigselect”>
<option value=”1″>Chocolate</option>
<option value=”2″>Vanilla</option>
<option value=”3″>”Heavenly” Hash</option>
<option value=”4″>Diet Cardboard</option>
</select>

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Splitting Data Across Multiple Pages}

The ADODB_Pager class, defined in the adodb-pager.inc.php file, provides a class that formats and displays a certain number of the rows in a record set with appropriate links to display more of the record set on other pages. You provide a database connection and a query when creating an ADODB_Pager object. The Render() method displays the results, shown in Figure 2-3.

$pager = new ADODB_Pager($conn,”SELECT _
              id,flavor,calories,price FROM _
              ice_cream_big”);
$pager->Render();


Figure 2-3.
ADODB_Pager displays a record set with pagination links.

The data is displayed in a table with formatting and navigation links. The leftmost link in the table goes to the first page, the second link goes to the previous page, the third link goes to the next page, and the rightmost link goes to the last page of data. The links contain a variable that controls what page is displayed, but they don’t automatically include other GET or POST variables that may have been set. By default, ADODB_Pager displays ten rows per page. You can override that by passing an argument to Render() with the number of rows per page you want. The following code generates a five-row table, shown in Figure 2-4:

$pager = new ADODB_Pager($conn,”SELECT id,flavor, _
                         calories,price FROM ice_cream_big”);
$pager->Render(5);


Figure 2-4.
ADODB_Pager can display a specified number of rows from a record set.

Internally, ADODB_Pager uses the rs2html() function to generate the HTML for the table. It overwrites the global variable $gSQLBlockRows with the number of rows per page it is displaying.

If you put more than one pager object in a page, ADODB_Pager needs to tell them apart so the next and previous links advance the correct pager. To accomplish this, each pager object needs a unique ID. The ID is specified as the third argument to the ADODB_Pager constructor. The default is adodb. To use two pager objects in one page, specify different IDs when you create the objects. The following code creates one pager with five rows and one with two rows:

$pager1 = new ADODB_Pager($conn,”SELECT id,flavor,calories, _
price FROM ice_cream_big”,’big’);
$pager1->Render(5);

$pager2 = new ADODB_Pager($conn,”SELECT id,flavor,calories, _
price FROM ice_cream”, ‘little’);
$pager2->Render(2);

Figure 2-5 shows the two pager tables.


Figure 2-5.
ADODB_Pager can display multiple tables on a page.

You can also include direct links to individual pages in the navigation by passing true as the fourth argument to the ADODB_Pager constructor. The following code creates a three-row table with individual page links:

$pager = new ADODB_Pager($conn,”SELECT id,flavor, _
                 calories,price FROM ice_cream_big”,’big’,true);
$pager->Render(3);

Figure 2-6 shows the table.


Figure 2-6.
ADODB_Pager can display navigation links to individual pages in the record set.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Pivot Tables}

Another handy feature of ADODB is its generation of SQL for pivot tables. Also known as cross-tabs (which, in turn, is short for cross-tabulations), pivot tables are a way of summarizing totals across multiple categories. You can create a pivot table that summarizes how many of each flavor various ice cream eaters have ordered. This requires the introduction of two new tables: eaters, which holds information about people eating ice cream, and orders, which holds information about which flavors the eaters order to eat. These tables look like this:

CREATE TABLE eaters (
   id int(10) unsigned NOT NULL,
   name varchar(255) default NULL,
   PRIMARY KEY (id)
);
CREATE TABLE orders (
   eater_id int(11) default NULL,
   ice_cream_id int(11) default NULL
);

Both tables need to be populated with some sample data:

INSERT INTO eaters VALUES (1,’Ireneo Funes’);
INSERT INTO eaters VALUES (2,’Bioy Casares’);
INSERT INTO eaters VALUES (3,’John Vincent Moon’);
INSERT INTO eaters VALUES (4,’Teodelina Villar’);
INSERT INTO orders VALUES (1,2);
INSERT INTO orders VALUES (1,3);
INSERT INTO orders VALUES (1,4);
INSERT INTO orders VALUES (2,4);
INSERT INTO orders VALUES (2,3);
INSERT INTO orders VALUES (3,1);
INSERT INTO orders VALUES (4,1);
INSERT INTO orders VALUES (4,4);
INSERT INTO orders VALUES (2,4);
INSERT INTO orders VALUES (3,1);

The following example generates a pivot table summarizing how many of each flavor each person has ordered. The record set that holds the results has one row for each flavor and one column for each person. It also has a column for the total number of orders per flavor. The PivotTableSQL() function generates the SQL query that creates the record set that holds the pivot table results. You need to pass it five arguments. The first is a connection handle. The second is a string containing a comma-separated list of tables from which to retrieve data. The third argument is the name of the database field that should be used for the rows of the table, and the fourth argument is the name of the field that should be used for the columns of the table. The last argument is the appropriate SQL to link together the different tables in the query. In this example, you join eaters and orders on eaters.id and orders.eater_id, and you join ice_cream and orders on ice_cream.id and orders.ice_cream_id. The PivotTableSQL() function is defined in a separate file, pivottable.inc.php, so you require that at the beginning of the example:

// Load the pivot table code
require ‘adodb/pivottable.inc.php’;
// Load the rs2html code
require ‘adodb/tohtml.inc.php’;

// Connect to the database
$conn = &ADONewConnection(‘mysql’);
$conn->connect(‘localhost’,'phpgems’,'phpgems1′,’phpgems’);

// Generate the query
$sql = PivotTableSQL($conn,
                ‘eaters, ice_cream, orders’,
                ‘ice_cream.flavor’,
                ’name’, 
                ’eaters.id = orders.eater_id and ‘ . 
                ’ice_cream.id = orders.ice_cream_id’);

// Send the query to the database and get a record set
$rs = $conn->execute($sql);
// Display the results
rs2html($rs);

Figure 2-7 shows the pivot table from this example.


Figure 2-7.
A pivot table summarizes totals across categories.

Each row contains the number of orders each person placed for that row’s flavor, as well as the total number of orders for that flavor. Each column contains the number of orders that column’s person placed for each flavor.

Instead of passing PivotTableSQL() a single column name as a fourth argument, you can also pass an array of labels and SQL expressions. PivotTable() treats each of the labels as column headers and uses the SQL expressions to determine what values are grouped into each column. For example, the following code summarizes each person’s orders grouped into calorie ranges:

$sql = PivotTableSQL($conn, 
         ’eaters, ice_cream, orders’, 
         ’name’, 
        array(‘diet’ => ‘calories > 0 and calories <= 20′, 
         ’fatty’ => ‘calories > 30′, 
         ’bland’ => ‘calories = 0′), 
         ’eaters.id = orders.eater_id and ice_cream.id = orders.ice_cream_id’);

$rs = $conn->execute($sql);
rs2html($rs);

Figure 2-8 shows the pivot table this code generates.


Figure 2-8
. A pivot table can use custom labels for categories.

In this pivot table, each row contains the number of orders placed by that row’s person for a flavor whose calories fall into each category: diet for flavors with more than 0 but fewer than or equal to 20 calories, fatty for flavors with more than 30 calories, and bland for flavors with no calories. The total column contains the total number of orders placed by the row’s person.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Caching}

ADODB has a built-in caching mechanism that you can invoke using a special function to run your queries. Instead of calling Execute(), use CacheExecute() for a query. The first argument to CacheExecute() is how many seconds you want the result of the query cached. The rest of the arguments are the same as Execute():

// Cache this recordset for 10 seconds
$rs = $conn->CacheExecute(10,’SELECT flavor, price,
                 calories FROM ice_cream’);

Record sets are cached in individual files. The filename is calculated from the SQL query passed to CacheExecute() and information from the connection handle: the database type, the database name, and the database user. For the purpose of calculating a cache filename, these values are all case-sensitive. If you call CacheExecute() twice with two queries that are identical to the database but have different spacing or capitalization, the second call to CacheExecute() doesn’t retrieve the data from the cache—it sends the query to the database. The query, database type, database name, and database user must be exactly the same for arecord set to be retrieved from the cache.

By default, ADODB puts its cache files in subdirectories it creates under the /tmp directory. You can change this base cache directory by setting the value of the global variable $ADODB_CACHE_DIR to your chosen directory name. ADODB’s caching behavior requires you to consider the permissions on the cache files and directories carefully to make sure that sensitive data is not exposed. First, the user that your Web server is running as must have write permission to $ADODB CACHE_DIR. The cache subdirectories created under $ADODB_CACHE_DIR have their permissions set so that the Web server user and group can read and write to them, but other users have only execute permission. This opens two potential security holes. First, if a user can guess the name of a cache file, then they can read the data inside it. Second, if you are in a shared-hosting environment where all users’ sites are served by the same Web server process, any user can read all of the cache files. To deal with these potential security risks, set $ADODB_CACHE_DIR to a new directory that other users can’t access. Each user in a shared hosting environment should ideally have their own space in the file system that the Web server serving their site can read but other Web servers can’t.

Record sets expire from the cache when their time, as specified to CacheExecute(), expires, but you have two ways to explicitly expire a record set. The first is to call CacheExecute() with a time value of 0. This forces ADODB to send the query to the database and overwrite any possibly cached values with new results. The second way is to call the CacheFlush() method. With no arguments, CacheFlush() removes everything from the cache. If you pass an SQL query to CacheFlush(), it removes from the cache the record set that corresponds to that query.

Both CacheExecute() and CacheFlush() calculate cache filenames based on the SQL query after any placeholders have been replaced. Both the query that contains placeholders and the placeholder array must be identical for two queries to have the same cache file:

// Get inexpensive ice cream
$rs = $conn->CacheExecute(10, ‘SELECT flavor FROM ice_cream
              WHERE price < ?’, array(4));
// Purge the query from the cache
$conn->CacheFlush(‘SELECT flavor FROM ice_cream
                     WHERE price < ?’,array(4));

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Exporting Data}

The toexport.inc.php file defines some functions that export record sets as tab-delimited or Comma-Separated Value (CSV ) data. The rs2csv() function returns a string containing the record set as a series of CSV lines:

$rs = $conn->Execute(‘SELECT * FROM ice_cream’);
$csv = rs2csv($rs);

The rs2tab() function returns a string containing the record set as a series of lines with tab-delimited values:

Accessing Databases with ADODB

$rs = $conn->Execute(‘SELECT * FROM ice_cream’);
$csv = rs2tab($rs);

Both functions escape double quotes by doubling them. A double quote mark in a value is turned into “” in the output. Fields with double quotes in them are also surrounded by double quotes. The rs2csv() function also surrounds fields that contain commas with double quotes.

There are also versions of these functions that save the CSV or tab-delimited data directly to a file, called rs2csvfile() and rs2tabfile(). These functions should be passed a file handle as a second argument:

if ($fh = fopen(‘/tmp/ice_cream.csv’,'w’)) {
     $rs = $conn->Execute(‘SELECT * FROM ice_cream’);
     rs2csvfile($rs,$fh);
     fclose($fh);
} else {
     print “Can’t open /tmp/ice_cream.csv”;
}

Additionally, there are versions of rs2csv() and rs2tab() that print the exported string rather than returning it. They are called rs2csvout() and rs2tabout():

$rs = $conn->Execute(‘SELECT * FROM ice_cream’);
rs2tabout($rs);

Aside from the conversion of double quotes in values, all six export functions convert newlines in values to spaces in output. They also include a title line consisting of the column names in the record set. To suppress the title line, pass false as a last argument to any of the functions.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

One thought on “Accessing Databases with ADODB

[gp-comments width="770" linklove="off" ]