php - How to correct pagination in oracle? -
i showing user record of how many forms he/she has accepted , purpose using pagination in php. data fetched oracle database , shown in table. using pagination, when go on next page shows previous record's new records not shown.
<table> <tbody> <?php include('connect.php'); $user_name = $_session['username']; $sql = oci_parse($conn,"select * userm user_name = '$user_name'"); $result = oci_execute($sql); while(($row = oci_fetch_array($sql,oci_both)) != false ){ $approvedby = $row[1]; } if(!isset($_get['page'])){ $_get['page'] = 0; }else{ // convert page number integer $_get['page'] = (int)$_get['page']; } $varpage = $_get['page']; if($varpage == "" || $varpage == "1"){ $page1 = 0; }else{ $page1 = ($varpage*6)-6; } $approvedby = strtoupper($approvedby); $sql = oci_parse($conn,"select * formc upper(approvedby) = '$approvedby' , rownum < 7"); oci_execute($sql); while(($row = oci_fetch_array($sql,oci_both)) != false ) { $accountingno = $row[0]; ?> <tr> <td style="color:black"><?php echo $row[0] ?></td> <td style="color:black"><?php echo $row[1] ?></td> <td style="color:black"><?php echo $row[2] ?></td> <td style="color:black"><?php echo $row[3]?></td> <td style="color:black"><?php echo $row[4] ?></td> <td style="color:black"><?php echo $row[7] ?></td> </tr> <?php } ?> </tbody> </table> <?php $sql1 = oci_parse($conn,"select * formc upper(approvedby) = '$approvedby'"); oci_execute($sql1); $count = oci_fetch_all($sql1,$abc); $a=$count/6; $a = ceil($a); for($b=1;$b<=$a;$b++){ ?> <a href="approvedlist.php?page=<?php echo $b?>" style="text-decoration;align:center: none;" class="btn btn-success "><?php echo $b." "; ?></a> <?php } oci_close($conn); ?> </div>
this explained in the underground php , oracle manual, page 181:
the canonical paging query oracle8i onwards given on http://asktom.oracle.com:
select * ( select a.*, rownum rnum (your_query_goes_here -- including order by) rownum <= max_row ) rnum >= min_row
here, min_row row number of first row , max_row row number of last row return.
there full example script:
<?php $c = oci_connect('hr', 'welcome', 'localhost/xe'); $mystmt = "select city locations order city"; $minrow = 4; // row number of first row return $maxrow = 8; // row number of last row return $pagesql = "select * ( select a.*, rownum rnum ( $mystmt ) rownum <= :maxrow) rnum >= :minrow"; $s = oci_parse($c, $pagesql); oci_bind_by_name($s, ":maxrow", $maxrow); oci_bind_by_name($s, ":minrow", $minrow); oci_execute($s); oci_fetch_all($s, $res); var_dump($res); ?>
edit:
as mentioned christopher jones, in oracle >=12.1 there cleaner way using offset clause:
select * mytable order myfield offset x rows fetch next y rows
Comments
Post a Comment