pagination+oracle

zuraifah

Hi there,

I'm still new with php. MOst of the php codes are using mySQL for their database, but I'm using Oracle as my database. I'm trying to pagination that will display results in multiple pages. I've tried one of the examples that I have but the it did not display results 15 per pages. I'm stuck. Been browsing but hopeless. Below are the codes:

<body>
<?
$conn = OCILogon("username","password","database");

if (!($limit)){
$limit = 15; // Default results per-page.
}

if ( !$page or $page < 0 ) {
$page = 0; // Default page value.
}

$sql = OCIParse($conn, "select acad_code from acad");
$numrows = OCIRowCount($sql);
OCIExecute($sql);

while (OCIFetch($sql)) {
$acad_code = OCIResult($sql,1);

if ($sql == 0){
echo("No results found matching your query"); // modify the "Not Found" error for your needs.
exit();
}
$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++; // has remainder so add one page
}

$current = intval($page/$limit) + 1; // Current page number.

if (($pages < 1)) {
$total = 1; // If $pages is less than one, total pages is 1.
}

else {
$total = $pages; // Else total pages is $pages value.
}

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit; //If not last results page, last result equals $page plus $limit.
}

else{
$last = $numrows; // If last results page, last result equals total number of results.
}

//echo $acad_code;

echo " ";
?>
<table width="100%" border="0">
<tr>
<td colspan="2" align="left">Results
per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a>
| <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a>
| <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a>
| <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a> 
</td>
</tr>
<tr>
<td colspan="2" align="left"> </td>
</tr>
<tr>
<td width="50%" align="left" valign="top">Showing
Results 
<?=$first?>
 - 
<?=$last?>
 of 
<?=$numrows?>
 </td>
<tr>
<td height="21" colspan="2" align="left">     </td>
</tr>

<?
$limitnum = 15;
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit&order=$order\"><< Back</a>\n");
}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("$i\n");} // If current page don't give link, just text.
//elseif ($ppage < $page - $limitnum || $ppage > $page + $limitnum){}
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit&order=$order\">$i</a>\n");}
}

if (!((($page+$limit) / $limit) >= $pages) &&
$pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo(" <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit&order=$order\">Next >></a>");
}
echo $acad_code;
echo " "; }?>
</body>
</html>

😕

planetsim

I havent used Oracle, but the SQL should be similar to that of MySQL of course it will have some differences in how to limit from the database.

Anyway

$sql = OCIParse($conn, "select acad_code from acad LIMIT $limit, $page");//this may need changing im not sure how Oracle SQL is
$numrows = OCIRowCount($sql);
OCIExecute($sql);
//you need will need a second query that doesnt have the limit to get total rows

amrigo

I am not sure if oracle has the use limit. Try rownum<15 instead