Last updated: Fri, 06 Nov 2009

oci_fetch_all

(PHP 5, PECL oci8 >= 1.1.0)

oci_fetch_all — Fetches all rows of result data into an array

Description

int oci_fetch_all ( resource $statement , array &$output [, int $skip = 0 [, int $maxrows = -1 [, int $flags = 0 ]]] )

Fetches all the rows from a result into a user-defined array.

For details on the data type mapping performed by the oci8 driver, see the datatypes supported by the driver

Parameters

statement

A valid OCI statement identifier.

output

Note: This function sets NULL fields to the PHP NULL value.

skip

The number of initial rows to ignore when fetching the result (default value of 0, to start at the first line).

maxrows

The number of rows to read, starting at the skip th row (default to -1, meaning all the rows).

flags

Parameter flags can be any combination of the following:

OCI_FETCHSTATEMENT_BY_ROW
OCI_FETCHSTATEMENT_BY_COLUMN (default value)
OCI_NUM
OCI_ASSOC

Return Values

Returns the number of rows fetched or FALSE in case of an error.

Examples

Example #1 oci_fetch_all() example


<?php
/* oci_fetch_all example mbritton at verinet dot com (990624) */

$conn = oci_connect("scott", "tiger");

$stmt = oci_parse($conn, "select * from emp");

oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);
if ($nrows > 0) {
   echo "<table border=\"1\">\n";
   echo "<tr>\n";
   foreach ($results as $key => $val) {
      echo "<th>$key</th>\n";
   }
   echo "</tr>\n";

   for ($i = 0; $i < $nrows; $i++) {
      echo "<tr>\n";
      foreach ($results as $data) {
         echo "<td>$data[$i]</td>\n";
      }
      echo "</tr>\n";
   }
   echo "</table>\n";
} else {
   echo "No data found<br />\n";
}
echo "$nrows Records Selected<br />\n";

oci_free_statement($stmt);
oci_close($conn);
?>

Notes

Note: In PHP versions before 5.0.0 you must use ocifetchstatement() instead. This name still can be used, it was left as alias of oci_fetch_all() for downwards compatability. This, however, is deprecated and not recommended.

oci_fetch_array

oci_execute

Last updated: Fri, 06 Nov 2009

add a note User Contributed Notes
oci_fetch_all

sixd at php dot net
23-Jan-2009 12:45


Oci_fetch_all() will return data only the first time it is called after an oci_execute(). It cannot be used to "page" data.

sixd at php dot net
20-Nov-2008 02:24


Avoid using the "skip" and "maxrows" parameters. The function works by fetching and discarding all rows up to the "skip'th" row.  This wastes network and CPU.  It is better to rewrite the query and use ROWNUM (or similar) to limit the rows the DB returns.

dawid.krysiak.net.pl
11-Jul-2007 12:55


It looks like passing 0 (zero) as a $maxrows parameter is treated identically as -1 value - all the rows are returned.



OCIfetchstatement($stmt, $res, 1, 0, OCI_FETCHSTATEMENT_BY_ROW)



Tested under: PHP 4.4.3, WinXP pro

stry_cat at yahoo dot com
07-Feb-2007 03:43


If you want to use more than one flag, you need to use the + to connect them:

<?php

oci_fetch_all($stmt, $row, "0", "-1", OCI_ASSOC+OCI_FETCHSTATEMENT_BY_ROW);



?>

jnavratil at houston dot rr dot com
12-Feb-2006 10:46


The number of rows returned is the number actually fetched, not the number potentially available.  If one limits the number of rows to fetch, the number of rows returned will be bound by that limit.  For example...



$nrows = oci_fetch_all($statement, $results, 0, 1);



would result in a returned value of '0' if there are no rows found for the statement, or '1' if any rows were found, regardless of the number actually present in the database.

larowlan at nospam dot com
26-Jun-2005 11:46


Not all column names are converted to upper case array keys. If you use double quotes around column aliases these retain their original case when converted to array keys.





Eg Select count(*) "record count" from table





will return an array with key 'record count' not 'RECORD COUNT'

phpnet at rafael dot eti dot br
09-Sep-2004 02:47


Humberto, from the previous note misunderstood the function ocifetchstatement. The default value of the last parameter is OCI_FETCHSTATEMENT_BY_COLUMN. That is the reason that $rows has the value 20 and count($arr) has the value 13 in:





$rows=ocifetchstatement ($st, &$arr, 0, 20);





It happens because his query probably had 13 columns.





That way, count($arr[0]) would have the value 20 that he expected of count($arr).





So,





$rows=ocifetchstatement ($st, &$arr, 0, 20);





is equivalent to





$rows=ocifetchstatement ($st, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_COLUMN);





and it is not equivalent to





$rows=ocifetchstatement ($st, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);





I hope this helps to avoid some confusion about ocifetchstatement.

humberto at humbertosilva dot com
09-Jul-2004 02:06


I had a problem with ocifetchstatement, it didn't returned nor more or less than 13 rows whatever the skip or num_rows was set to....





I found that it has a bug, you MUST specify the last parameter, so i added OCI_FETCHSTATEMENT_BY_ROW to the function call and it works fine.





Hope this helps someone out there ...





With:


           $rows=ocifetchstatement ($st, &$arr, 0, 20);





Returns:


           $rows � 20


           count($arr) � 13 !!! (should be 20)





Now here's the working version:





With:


           $rows=ocifetchstatement ($st, &$arr, 0, 20, OCI_FETCHSTATEMENT_BY_ROW);


Returns:


           $rows � 20


           count($arr) � 20





wich are the right values.





Regards,





Humberto Silva


http://www.humbertosilva.com/

adelac05 at sprintspectrum dot com
13-Mar-2003 12:40


One thing I found with this:





If you have two tables with the same column names, you will run into problems when displaying them, i.e:





$sql = "select a.name, b.name, c.phone


from table1 a, table2 b, table3 c";





php will overwrite the a.name display cell with the b.name value.  So you wanted three cells to display and you are only getting two.  i.e.





What you wanted:





----------------------------------------


| Bob           | Jones     | 555-555-5555 |


----------------------------------------





What you got:





-----------------------------------------


| Jones        | 555-555-5555 |               |


-----------------------------------------





Resolution:  rename your table column names as they come in.





$sql = "select a.name, b.name AS NAME2, c.phone


from table1 a, table2 b, table3 c";

lzilber at hotmail dot com
03-Aug-2001 09:19


Similar to the closed bug #9520 reported by g.giunta@libero.it (it did not seem obvious to me):<br>


After a call to OCIFetchStatement, functions OCIColumnName, OCIColumnSize, OCIColumnType, ... will NOT work anymore on the fetched statement.<br>


Explanation (thies@php.net): "...after a cursor is completly read (which is what fechstatement does) this information is no longer valid."


</p>

aries at bsd dot hu
29-Dec-2000 10:41


Don't forget: you must use capital letters as column name.

waycool at audioload dot com
31-Oct-1999 09:44


Watch out for select statements which have columns with the same name (e.g. select a.name, b.name from ...) because the later results will wipe out the previous $data["name"] values. One way to get around this is to use aliases (e.g. select a.name as a_name, b.name as b_name ...)

add a note

oci_fetch_array

oci_execute

Last updated: Fri, 06 Nov 2009