This discussion is archived
6 Replies Latest reply: Feb 28, 2012 3:46 PM by cj RSS

PHP/ORACLE Pagination

604901 Newbie
Currently Being Moderated
Hi All,

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

Thanks
Sam
  • 1. Re: PHP/ORACLE Pagination
    cj Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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