| PEAR Handbuch | ||
|---|---|---|
| Zurück | Nach vorne | |
The DB_Result object provides two functions to fetch rows: fetchRow() and fetchInto(). fetchRow() returns the row, null on no more data or a DB_Error, when an error occurs. fetchInto() requires a variable, which be will directly assigned by reference to the result row. It will return null when result set is empty or a DB_Error too.
<?php
...
$db = DB::connect($dsn);
$res = $db->query("select * from mytable");
// Get each row of data on each iteration until
// there is no more rows
while ($row = $res->fetchRow()) {
$id = $row[0];
}
// Or:
// an example using fetchInto()
while ($res->fetchInto($row)) {
$id = $row[0];
}
?> |
The fetch modes supported are:
DB_FETCHMODE_ORDERED (default)
The fetch*() returns an ordered array. The order is taken from the select statment.
<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_ORDERED);
/*
$row will contain:
array (
0 => <column "id" data>,
1 => <column "name" data>,
2 => <column "email" data>
)
*/
// Access the data with:
$id = $row[0];
$name = $row[1];
$email = $row[2];
?> |
DB_FETCHMODE_ASSOC
Returns an associative array with the column names as the array keys
<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_ASSOC);
/*
$row will contain:
array (
'id' => <column "id" data>,
'name' => <column "name" data>,
'email' => <column "email" data>
)
*/
// Access the data with:
$id = $row['id'];
$name = $row['name'];
$email = $row['email'];
?> |
DB_FETCHMODE_OBJECT
Returns a DB_row object with column names as properties
<?php
$res = $db->query('select id, name, email from users');
$row = $res->fetchRow(DB_FETCHMODE_OBJECT);
/*
$row will contain:
db_row Object
(
[id] => <column "id" data>,
[name] => <column "name" data>,
[email] => <column "email" data>
)
*/
// Access the data with:
$id = $row->id;
$name = $row->name;
$email = $row->email;
?> |
You can set the fetch mode for every call or for your whole DB instance.
<?php
// 1) Set the mode per call:
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$id = $row['id'];
}
// 2) Set the mode for all calls:
$db = DB::connect($dsn);
// this will set a default fetchmode for this Pear DB instance
// (for all queries)
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$result = $db->query(...);
while ($row = $result->fetchRow()) {
$id = $row['id'];
}
?> |
The PEAR DB fetch system also supports an extra parameter to the fetch statement. So you can fetch rows from a result by number. This is especially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.
<?php
...
// the row to start fetching
$from = 50;
// how many results per page
$res_per_page = 10;
// the last row to fetch for this page
$to = $from + $res_per_page;
foreach (range($from, $to) as $rownum) {
if (!$row = $res->fetchrow($fetchmode, $rownum)) {
break;
}
$id = $row[0];
....
}
?> |
It is recommended to finish the result set after processing in order to to save memory. Use free() to do this.
<?php
...
$result = $db->query('SELECT * FROM clients');
while ($row = $result->fetchRow()) {
...
}
$result->free();
?> |
PEAR DB provides some special ways to retrieve information from a query without the need of using fetch*() and loop throw results.
getOne() retrieves the first result of the first column from a query
$numrows = $db->getOne('select count(id) from clients'); |
getRow() returns the first row and return it as an array
$sql = 'select name, address, phone from clients where id=1';
if (is_array($row = $db->getRow($sql))) {
list($name, $address, $phone) = $row;
} |
getCol() returns an array with the data of the selected column. It accepts the column number to retrieve as the second param.
$all_client_names = $db->getCol('select name from clients'); |
getAssoc() fetches the entire result set of a query and return it as an associative array using the first column as the key.
$data = $db->getAssoc('SELECT name, surname, phone FROM mytable')
/*
Will return:
array(
'Peter' => array('Smith', '944679408'),
'Tomas' => array('Cox', '944679408'),
'Richard' => array('Merz', '944679408')
)
*/ |
getAll() fetches all the rows returned from a query
$data = $db->getAll('SELECT id, text, date FROM mytable');
/*
Will return:
array(
1 => array('4', 'four', '2004'),
2 => array('5', 'five', '2005'),
3 => array('6', 'six', '2006')
)
*/ |
The get*() family methods will do all the dirty job for you, this is: launch the query, fetch the data and free the result. Please note that as all PEAR DB functions they will return a PEAR DB_error object on errors.
With PEAR DB you have many ways to retrieve useful information from query results. These are:
numRows(): Returns the total number of rows returned from a "SELECT" query.
// Number of rows echo $res->numRows(); |
numCols(): Returns the total number of columns returned from a "SELECT" query.
// Number of cols echo $res->numCols(); |
affectedRows(): Returns the number of rows affected by a data manipulation query ("INSERT", "UPDATE" or "DELETE").
// remember that this statement won't return a result object
$db->query('DELETE * FROM clients');
echo 'I have deleted ' . $db->affectedRows() . ' clients'; |
tableInfo(): Returns an associative array with information about the returned fields from a "SELECT" query.
// Table Info print_r($res->tableInfo()); |
| Zurück | Zum Anfang | Nach vorne |
| Query | Nach oben | Sequences |