Introduction - Fetch
Description
Fetch functions
In order to fetch data from a result resource you can use one if the following methods: fetchInto() , fetchOne() . , fetchRow() . , fetchCol() . and fetchAll() . All above mentioned methods except fetchOne() return the requested data encapsuled into a (multi-dimensional-)array, NULL on no more data or a MDB_Error , when an error occurs. All method prefixed with fetch() automatically free the result set.
Fetching a result set
<?php
...
$db = MDB::connect($dsn);
$res = $db->query("SELECT * FROM mytable");
// Get each row of data on each iteration until
// there are no more rows
while ($row = $db->fetchInto($res)) {
$id = $row[0];
}
// If we are just interested in the first column of the first row
$id = $db->fetchOne($res);
// Since the fetch methods always free the result set
// we cannot loop across the result set but instead
// need to choose the proper fetch method
$data = $db->getAll($res);
foreach($data as $row)
{
$id = $row[0];
}
?>
Select the format of the fetched row
The fetch modes supported are:
-
MDB_FETCHMODE_ORDERED (default)
The fetch*() returns an ordered array. The order is taken from the select statement.
Fetch a ordered array
<?php
$res = $db->query('SELECT id, name, email FROM users');
$row = $db->fetchRow($res, MDB_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];
?> -
MDB_FETCHMODE_ASSOC
Returns an associative array with the column names as the array keys
Fetch a assoc. array
<?php
$res = $db->query('SELECT id, name, email FROM users');
$row = $db->fetchRow($res, MDB_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'];
?>
Set the format of the fetched row
You can set the fetch mode per result call or for your whole MDB instance.
Per call
<?php
while ($row = $db->fetchInto($res, MDB_FETCHMODE_ASSOC)) {
$id = $row['id'];
}
?>
Once per instance
<?php
$db = MDB::connect($dsn);
// this will set a default fetchmode for this Pear MDB instance
// (for all queries)
$db->setFetchMode(MDB_FETCHMODE_ASSOC);
$result = $db->query(...);
while ($row = $db->fetchRow($res)) {
$id = $row['id'];
}
?>
Fetch rows by number
The PEAR MDB 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.
Fetching by number
<?php
...
// the row to start fetching
$from = 50;
// how many results per page
$resPage = 10;
// the last row to fetch for this page
$to = $from + $resPage;
foreach (range($from, $to) as $rowNum) {
if (!$row = $db->fetchInto($res, $fetchmode, $rowNum)) {
break;
}
$id = $row[0];
....
}
?>
Freeing the result set
It is recommended to finish the result set after processing in order to to save memory. Use freeResult() to do this.
Freeing
<?php
...
$res = $db->query('SELECT * FROM clients');
while ($row = $res->fetchInto($res)) {
...
}
$db->freeResult($res);
?>
Quick data retrieving
MDB provides some special ways to retrieve information from a query without the need of using fetch*() and loop throw results.
queryOne() retrieves the first result of the first column from a query
<?php
$numrows = $db->queryOne('select count(id) from clients');
?>
queryRow() returns the first row and returns it as an array.
<?php
$sql = 'select name, address, phone from clients where id=1';
if (is_array($row = $db->queryRow($sql))) {
list($name, $address, $phone) = $row;
}
?>
queryCol() returns an array with the data of the selected column. It accepts the column number to retrieve as the second parameter.
<?php
$all_client_names = $db->queryCol('SELECT name FROM clients');
?>
The above sentence could return for example:
<?php
$all_client_names = array('Stig', 'Jon', 'Colin');
?>
getAll() fetches all the rows returned from a query. This method also has some advanced parameters still will also enable you to return the data as an associative array using the first column as the key.
<?php
$data = 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 query*() 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 MDB functions they will return a MDB_Error object on errors.
Getting more information from query results
With MDB 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.
<?php
// Number of rows
echo $db->numRows($res);
?> -
numCols() : Returns the total number of columns returned from a "SELECT" query.
<?php
// Number of cols
echo $db->numCols($res);
?> -
affectedRows() : Returns the number of rows affected by a data manipulation query ("INSERT", "UPDATE" or "DELETE").
<?php
// 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.
<?php
// Table Info
print_r($db->tableInfo($res));
?>
Don't forget to check if the returned result from your action is a MDB_Error object. If you get a error message like "MDB_Error: database not capable", means that your database backend doesn't support this action.