Error when fetching the data in PHP

[web_editor]

I have PHP script that will display data dynamically and have pagination, but I got an error like this

Warning: oci_fetch_array() [function.oci-fetch-array]: ORA-01002: fetch out of sequence

I’m using both oci_fetch_all and oci_fetch_array since I’m using Oracle database.

Please help me to fix this. Thanks

$sqlQuery = "SELECT * FROM table";
$objParse = oci_parse($conn, $sqlQuery);
oci_execute ($objParse,OCI_DEFAULT);

//Pagination
$Num_Rows = oci_fetch_all($objParse, $Result);
$Per_Page = 100;

$Page = $_GET["Page"];
if(!$_GET["Page"]){
    $Page=1;
}

$Prev_Page = $Page-1;
$Next_Page = $Page+1;

$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows<=$Per_Page){
    $Num_Pages =1;
} else if(($Num_Rows % $Per_Page)==0){
    $Num_Pages =($Num_Rows/$Per_Page) ;
} else {
    $Num_Pages =($Num_Rows/$Per_Page)+1;
    $Num_Pages = (int)$Num_Pages;
}

$Page_End = $Per_Page * $Page;
if ($Page_End > $Num_Rows){
    $Page_End = $Num_Rows;
}

//Fetch the column name
echo "<div class="datatable">";
echo "<table cellspacing="0" cellpadding="0" border="0">n";
$ncols = oci_num_fields($objParse);
echo "<thead>";
echo "<tr class="title">n";
for ($i = 1; $i <= $ncols; ++$i){
    $colname = oci_field_name($objParse, $i);
    echo "  <th><b>".htmlentities($colname, ENT_QUOTES)."</b></th>n";
}
    echo "<th><b>Action</b></th>";
echo "</tr>n";
echo "</thead>";

for($i=$Page_Start;$i<$Page_End;$i++){

    while (($row = oci_fetch_array($objParse, OCI_ASSOC+OCI_RETURN_NULLS)) != false){
        echo "<tbody>n";
            echo "<tr>n";
    foreach ($row as $item){
            echo "<td>".($item !== null ? htmlentities($item, ENT_QUOTES):" ")."</td>n";
    }
            echo "<td><a href='pagination_oracle2.php?EmpID=".$row['EMPLOYEE_ID'][$i]."'>Edit</a></td>";
            echo "</tr>n";
        echo "</tbody>n";
    }
}
    echo "</table>n";
?>
<div class="pagination">
Total: <?= $Num_Rows;?> | Record: <?php echo $Num_Pages;?> |
<br>Page:
<?
if($Prev_Page){
    echo "<a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> ";
}

for($i=1; $i<=$Num_Pages; $i++){
    if($i != $Page){
        echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]";
    } else {
        echo "<b> $i </b>";
    }
}

if($Page!=$Num_Pages){
    echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next >></a> ";
    echo "</div>";
}

[bochristensen]

I don’t have any experience with oracle, but could it be because you are running two oci_fetch_* on the same execute?

[web_editor]

I see. How to fix this? How can I add pagination without using another oci_fetch_all? Thanks!

[bochristensen]

From a quick look here: http://php.net/manual/en/function.oci-fetch-all.php I would say that the first oci_fetch_all is enough.

$Num_Rows = oci_fetch_all($objParse, $Result);

$Result is a two-dimensional array with all the fetched rows.

[web_editor]

@bochristensen can you pls give me the right full code, i’m newbie in PHP? so i can study it.

[bochristensen]

No I’m not going to install a Oracle database and make a pagination script for you.

Try to do a google search for oci8 AND pagination

[Author]

Posts