Accessing Databases with ADODB - Retrieving Multiple Rows

(Page 4 of 12 )

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 = \$%.02f\n",
          $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.

Next: Understanding Error Handling >>
 

More Database Articles
More By Apress Publishing

Comments On: Accessing Databases with ADODB

· i want the programming code for the add delete update useing the adodb ontrols.   >>> Post your comment now!