Accessing Databases with ADODB - Introducing Record Sets
(Page 3 of 12 )
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. |
Next: Retrieving Multiple Rows >>
More Database Articles
More By Apress Publishing
/ 37 
