Last updated: Fri, 06 Nov 2009

oci_fetch_all

(PHP 5, PECL oci8 >= 1.1.0)

oci_fetch_all — 結果データの全ての行を配列に取得する

説明

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

全ての行の結果をユーザー定義の配列に格納して取得します。

oci8 ドライバによるデータ型マッピングの詳細については、ドライバがサポートするデータ型を参照ください。

パラメータ

statement

有効な OCI ステートメント ID。

output

注意: この関数は、 NULL フィールドに PHPの NULL 値を設定します。

skip

結果を取得する際に無視する行数 (デフォルトの値は 0 で、最初の行から開始されます) 。

maxrows

読み込む行数。 skip 番目の行から開始されます (デフォルトは -1 で、全ての行を意味します)。

flags

パラメータ flags には次の値の組み合わせが可能です。

OCI_FETCHSTATEMENT_BY_ROW
OCI_FETCHSTATEMENT_BY_COLUMN (デフォルト値)
OCI_NUM
OCI_ASSOC

返り値

取得した行数、失敗した場合 FALSE を返します。

例

例1 oci_fetch_all() の例


<?php
/* oci_fetch_all の例 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);
?>

注意

注意: PHP バージョン 5.0.0 以前では、代わりに ocifetchstatement() を使用しなければなりません。まだこの名前を使用することができ、下位互換性のため oci_fetch_all() への別名として残されていますが、推奨されません。

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