Paging Result sets in Oracle

[Elknox]

I am using Ora_logon to access an Oracle 8 database. How do I get php to display 20 rows at a time with the option of "Next" and "Previous".

[toma42]

First off, you should use the oci interface (that stands for oracle call interface, if you didn't know). Next, read the pear base class for oci8 that is distributed with PHP (found in /DB/oci8.php)

That class has a function, modifyLimitQuery() by Tomas V.V.Cox, that will give you a limited result set. To figure out how many previous and nexts you need, do a count(*) on your query first.

[Elknox]

I am chasing a deadline and the whole site I have used Ora_logon and to try to change now is a little late. Is there no way of archiving the same result but using ORA and not OCI?

[jmcneese]

$nrows = count($dbresult);
$from = $offset;
$numpages = ceil($nrows/$limit);

if ($nrows <= $offset+$limit) {
    $to = $nrows - 1;
} else {
    $to = $offset+$limit-1;
}

foreach (range($from,$to) as $rownum) {
    $row = $dbresult[$rownum];
    /* 
    do stuff here with $row
    */
}

if ($numpages > 1) {

$pagebits = "Page: ";

for ($i=0;$i<$numpages;$i++) {
    $newoffset = $limit * ($i);
    $pg = $i + 1;
    if ($page == $pg) {
        $pagebits .= "<b>$pg</b>";
    } else {
        $pagebits .= "<a href=\"$PHP_SELF?offset=$newoffset&limit=$limit&page=$pg\">$pg</a>&nbsp;";
    }
}

if ($offset > 1) {
    $prevoffset = $offset - $limit;
    $prevpage = $page - 1;
    $previous = "<a href=\"$PHP_SELF?offset=$prevoffset&limit=$limit&page=$pg\">Previous $limit records</a>";
} else {
    $previous = "&nbsp;";
}

if ($nrows > ($offset + $limit)) {
    $nextoffset = $offset + $limit;
    $nextpage = $page + 1;
    $next = "<a href=\"$PHP_SELF?offset=$nextoffset&limit=$limit&page=$pg\">Next $limit records</a>";
} else {
    $next = "&nbsp;";
}
}

now all you have to do is echo $next, $previous and $pagebits somewhere to give you an clickable index of results.

[toma42]

Elknox: help yourself before asking others to help you. I told you exacly where you could find example code for doing what you wanted to but your responce shows you didn't look at it.

[Elknox]

I am more familiar with MySQL as opposed to Oracle. This is the script I have been trying to use.

********browse_books.php**
<?php
require_once("books_fns.php");
do_html_header("Browse Book Titles");
function do_alphabet_link()
{
<p align='center'><font face='Arial' size='2'><b>
<a href='browse_books.php?letter=ALL'>ALL</a> |
<a href='browse_books.php?letter=A'>A</a> |
<a href='browse_books.php?letter=B'>B</a> |
<a href='browse_books.php?letter=C'>C</a> |
<a href='browse_books.php?letter=D'>D</a> |
<a href='browse_books.php?letter=E'>E</a> |
<a href='browse_books.php?letter=F'>F</a> |
<a href='browse_books.php?letter=G'>G</a> |
<a href='browse_books.php?letter=H'>H</a> |
<a href='browse_books.php?letter=I'>I</a> |
<a href='browse_books.php?letter=J'>J</a> |
<a href='browse_books.php?letter=K'>K</a> |
<a href='browse_books.php?letter=L'>L</a> |
<a href='browse_books.php?letter=M'>M</a> |
<a href='browse_books.php?letter=N'>N</a> |
<a href='browse_books.php?letter=O'>O</a> |
<a href='browse_books.php?letter=P'>P</a> |
<a href='browse_books.php?letter=Q'>Q</a> |
<a href='browse_books.php?letter=R'>R</a> |
<a href='browse_books.php?letter=S'>S</a> |
<a href='browse_books.php?letter=T'>T</a> |
<a href='browse_books.php?letter=U'>U</a> |
<a href='browse_books.php?letter=V'>V</a> |
<a href='browse_books.php?letter=W'>W</a> |
<a href='browse_books.php?letter=X'>X</a> |
<a href='browse_books.php?letter=Y'>Y</a> |
<a href='browse_books.php?letter=Z'>Z</a>
</b></font>
</p>
}
do_alphabet_link();

require("db_connect.php");//database connection file
$result_number = 20;
$letter = $_REQUEST['letter'];
if(!isset($num_pages)){
if(isset($letter)){
if($letter == "ALL"){
$query1 = "SELECT FROM Library ORDER BY bookTitle ASC";
}else{
$query1 = "SELECT FROM Library WHERE bookTitle LIKE '$letter%' ORDER BY bookTitle ASC";
}
}else {
$query1 = "SELECT FROM Library ORDER BY bookTitle ASC";
}
$query_result1 = Ora_Parse ($cursor, $query1);
if ($query_result1 == false){
echo Ora_ErrorCode($cursor).": ".Ora_Error($cursor)."<BR>";
exit;
}
$query_result1 = Ora_Exec ($cursor);
if ($query_result1 == false){
echo Ora_ErrorCode($cursor).": ".Ora_Error($cursor)."<BR>";
exit;
}
$num_results = count($cursor);
if ($num_results > $result_number){
$num_pages = ceil($num_results/$result_number);
}elseif($num_results > 0){
$num_pages = 1;
}
}
$start = 0;
}
if (isset($letter)) {
if($letter == "ALL"){
$query = "SELECT FROM Library ORDER BY bookTitle ASC LIMIT $start, $result_number";
}else{
$query = "SELECT FROM Library WHERE bookTitle LIKE '$letter%' ORDER BY bookTitle ASC LIMIT $start, $result_number";
}
}else {
$query = "SELECT FROM Library ORDER BY bookTitle ASC LIMIT $start, $result_number";
}
if (Ora_Fetch_Into ($cursor, &$values)){
echo "<p></p><table width=99% align='center' cellpadding='3' cellspacing='0' border='0'>
<tr><td width='100%' colspan='4' bgcolor='#C0C0C0' height='1'></td>
</tr>
<tr>
<td bgcolor='#FFFFEE' width='10%' valign='top'><font face='Arial' size='2'><b>Book Title</b></font>
</td>
<td bgcolor='#FFFFEE' width='40%' valign='top'><font face='Arial' size='2'><b>Author Name</b></font>
</td>
<td bgcolor='#FFFFEE' width='40%' valign='top'><font face='Arial' size='2'><b>Publisher Name</b></font>
</td>
</td>
<td bgcolor='#FFFFEE' width='10%' align='right' valign='top'><font face='Arial' size='2'><b>ISBN</b></font>
</td>
</tr>
<tr>
<td width='100%' colspan='4' bgcolor='#C0C0C0' height='1'>
</td>
</tr>
";
do {
$ID = $values[0];
$bookTitle = $values[1];
$AuthorName = $values[2];
$PublisherName = $values[3];
$ISBN = $values[4];
echo "
<tr>
<td valign='top'>
<font face='Arial' size='2'><a href='view_book.php?ID=$ID'>$bookTitle</font></a>
</td>
<td valign='top'>
<font face='Arial' size='2'><i>$AuthorName</i></font>
</td>
<td valign='top'>
<font face='Arial' size='2'>$PublisherName</font>
</td>
<td align='right' valign='top'>
<font class='$Closed' face='Arial' size='2'>$ISBN</font>
</td>
</tr>
<tr>
<td width='100%' colspan='4' bgcolor='#C0C0C0' height='1'>
</td>
</tr>
";
if($num_pages > 1) {
echo "<tr><td colspan='2'>";
if($start == 0){
$current_page = 1;
}else{
$current_page = ($start/$dispay_number) + 1;
}
if($start !=0){
echo "<a href='browse_books?start=" . ($start - $result_number) . "&num_pages=" . $num_pages . "&letter=" . "'>Previous</a>";
}
for ($i = 1; $i <= $num_pages; $i++) {
$next_start = $start + $result_number;
if ($i != $current_page){
echo "<a href='browse_books.php?start=" . (($result_number * ($i - 1))) . "&num_pages=" . $num_pages . "&letter=" . $letter . "'>" . $i . "</a>";
}else{
echo $i . ' ';
}
}
if($current_page !=$num_pages){
echo "<a href='browse_books.php?start=" . ($start + $result_number) . "&num_pages=" . $num_pages . "&letter=" . $letter . "'>Next</a>";
}
echo "</td></tr>";
}
} while (Ora_Fetch_Into ($cursor, &$values));
echo "</table>";
do_alphabet_link();
} else {
echo "<p><font size='2'>There are no book titles beginning with the letter <b>$letter</b></font></p>";
}
Ora_Close ($cursor);
Ora_Logoff ($connection);
//place the page footer
do_html_footer();
?>

---