6 Replies Latest reply: Feb 28, 2012 5:46 PM by cj RSS

    PHP/ORACLE Pagination

    604901
      Hi All,

      Can any one help me to create PHP/ORACLE Pagination..

      Thanks
      Sam
        • 1. Re: PHP/ORACLE Pagination
          cj
          The starting point is getting the query to return just the rows you want. See page 125 of the current version (1.4) of The Underground PHP and Oracle Manual

          -- cj
          • 2. Re: PHP/ORACLE Pagination
            454458
            There is an excellent article published by 'The Oracle + PHP Cookbook' entitled "Paged Result Sets with Oracle and PHP" by Harry Fuecks (November 2005).

            Just google 'oracle php cookbook' and download the article. I used it to set page constraints on a library applications (I limit the number of rows displayed to 20 rows).

            Here's the link:

            http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_paged.html
            • 3. Re: PHP/ORACLE Pagination
              823344
              Hi pals

              Here is a good easy sample of pagination using Oracle in PHP, Please notice that I have a layer, running different queries which I need, and in some lines below I've used them but the names of the function and variable which I've called are obviously seen that how they perform.

              Further, Please note that you would be expected to call your own oracle database through PHP oracle functions, and as you can see, I have a table storing page sessions and the function "oracle->select" runs the query "SELECT * FROM SESSION_ID" and returns its result and the variable "numberrows" has the number of rows which the implemented query has returned.

              <html>
              <head>
              <title>PAGING</title>
              </head>
              <body>
              <?
              session_start();
              $session_check = $orcl->oracle_select("SESSION_ID", "*");
              $Per_Page = 10; // Per Page
              $Num_Rows = $orcl->numberrows;
              if( !isset( $_GET["Page"] ) )
              {
                   $Page=1;
              }
              else
              {
              $Page = $_GET["Page"];
              }
              $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;
              }
              ?>
              <table width="800" border="1" cellpadding="0" cellspacing="0" style="font-size: 12px; font: Tahoma">
              <tr>
              <th width="400" align="center">CUSTOMER</th>
              <th width="400" align="center">Name</th>
              </tr>
              <?
              for($i=$Page_Start; $i<$Page_End; $i++)
              {
              ?>
              <tr>
              <td width="400" align="center"><div align="center"><?=$session_check["ID"][$i]; ?></div></td>
              <td width="400" align="center"><?=$session_check["USER_ID"][$i];?></td>
              </tr>
              <?
              }
              ?>
              </table>
              <table width="800" border="0" cellpadding="0" cellspacing="0" style="font-size: 12px; font: Tahoma">
              <tr>
              <td align="center"> </td>
              </tr>
              <tr>
              <td align="center">Total <?= $Num_Rows;?> Record : <?=$Num_Pages;?> Page :</td>
              </tr>
              <tr>
              <td align="center">
              <?
              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> ";
              }
              ?>
              </td>
              </tr>
              <tr>
              <td align="center"> </td>
              </tr>
              </table>
              </body>
              </html>
              • 4. Re: PHP/ORACLE Pagination
                920667
                Only Oracle query for pagination given in the manual 'The Underground PHP and Oracle Manual', can any one help me with the PHP code for pagination with Oracle database?

                Thanks in advance.
                • 5. Re: PHP/ORACLE Pagination
                  920667
                  The best example if have found is this - http://www.shotdev.com/php/php-oracle/php-oracle-oci8-and-paging-pagination/
                  • 6. Re: PHP/ORACLE Pagination
                    cj
                    The page you quoted has one big problem: it does an oci_fetch_all() and then only prints a few rows.
                    This is very wasteful of RDBMS processing and network transfer costs to find & ship all the rows back to PHP.

                    The PHP code for paging is not specific to Oracle, and there are plenty of examples on the web.

                    Here's another one which does use Oracle DB:
                    http://docs.oracle.com/cd/E17781_01/appdev.112/e18555/ch_five_emp_data.htm#sthref140

                    Edited by: cj on Feb 28, 2012 3:45 PM Added comment on the previously referenced page