DB_common::getAll()
Synopsis
array &getAll (
string $query
,
array $params = array()
,
integer $fetchmode
= DB_FETCHMODE_DEFAULT
)
Description
Runs the query provided and puts the entire result set into a nested array then frees the result set.
Parameter
-
string
$query -
the SQL query or the statement to prepare
-
array
$params -
array to be used in execution of the statement. Quantity of array elements must match quantity of placeholders in query.
If supplied, prepare()/ execute() is used.
This method does not allow scalars to be used for this argument.
-
integer
$fetchmode -
the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. The current fetch mode can be changed using setFetchMode(). Potential values include:
-
DB_FETCHMODE_ORDERED
-
DB_FETCHMODE_ASSOC
-
DB_FETCHMODE_OBJECT
-
DB_FETCHMODE_ORDERED | DB_FETCHMODE_FLIPPED
-
DB_FETCHMODE_ASSOC | DB_FETCHMODE_FLIPPED
-
Return value
array - a nested array or a DB_Error on failure
Throws
| Error code | Error message | Reason | Solution |
|---|---|---|---|
| DB_ERROR_INVALID | invalid | SQL statement for preparing is not valid. | See the prepare() documentation, if you want to use a SQL statemt using placeholders. |
| DB_ERROR_MISMATCH | mismatch | Quantity of parameters didn't match quantity of placeholders in the prepared statement. |
Check that the number of placeholders in the
prepare() statement passed to
$query equals the count of entries
passed to $params.
|
| DB_ERROR_NODBSELECTED | no database selected | No database was chosen. | Check the DSN in connect(). |
| every other error code | Database specific error | Check the database related section of PHP-Manual to detect the reason for this error. In the most cases a misformed SQL statement. Ie. using LIMIT in a SQL-Statement for an Oracle database. |
Note
This function can not be called statically.
Example
Using getAll() to return an associative array by setting the default fetch mode first
<?php
// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$data =& $db->getAll('SELECT cf, nf, df FROM foo');
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => Array
(
[cf] => Juan
[nf] => 5
[df] => 1991-01-11 21:31:41
)
[1] => Array
(
[cf] => Kyu
[nf] => 10
[df] => 1992-02-12 22:32:42
)
)
Using getAll() to return an ordered array
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo',
array(), DB_FETCHMODE_ORDERED);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => Array
(
[0] => Juan
[1] => 5
[2] => 1991-01-11 21:31:41
)
[1] => Array
(
[0] => Kyu
[1] => 10
[2] => 1992-02-12 22:32:42
)
)
Using getAll() to return a flipped ordered array
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo',
array(), DB_FETCHMODE_ORDERED | DB_FETCHMODE_FLIPPED);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => Array
(
[0] => Juan
[1] => Kyu
)
[1] => Array
(
[0] => 5
[1] => 10
)
[2] => Array
(
[0] => 1991-01-11 21:31:41
[1] => 1992-02-12 22:32:42
)
)
Using getAll() to return an associative array
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo',
array(), DB_FETCHMODE_ASSOC);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => Array
(
[cf] => Juan
[nf] => 5
[df] => 1991-01-11 21:31:41
)
[1] => Array
(
[cf] => Kyu
[nf] => 10
[df] => 1992-02-12 22:32:42
)
)
Using getAll() to return a flipped associative array
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo',
array(), DB_FETCHMODE_ASSOC | DB_FETCHMODE_FLIPPED);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[cf] => Array
(
[0] => Juan
[1] => Kyu
)
[nf] => Array
(
[0] => 5
[1] => 10
)
[df] => Array
(
[0] => 1991-01-11 21:31:41
[1] => 1992-02-12 22:32:42
)
)
Using getAll() to return an array of objects
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo',
array(), DB_FETCHMODE_OBJECT);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => stdClass Object
(
[cf] => Juan
[nf] => 5
[df] => 1991-01-11 21:31:41
)
[1] => stdClass Object
(
[cf] => Kyu
[nf] => 10
[df] => 1992-02-12 22:32:42
)
)
Using getAll() in prepare/execute mode to return an associative array
<?php
// Once you have a valid DB object named $db...
$data =& $db->getAll('SELECT cf, nf, df FROM foo WHERE nf = ?',
array(5), DB_FETCHMODE_ASSOC);
if (PEAR::isError($data)) {
die($data->getMessage());
}
print_r($data);
?>
Output:
Array
(
[0] => Array
(
[cf] => Juan
[nf] => 5
[df] => 1991-01-11 21:31:41
)
)