oracle| Program | pagination
<! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 3.2 final//en" >
<HTML>
<HEAD>
<title>paging test</title>
<meta name= "generator" content= "TextPad 4.0" >
<meta name= "Author" content= "?" >
<meta name= "Keywords" content= "?" >
<meta name= "Description" content= "?" >
</HEAD>
<body bgcolor= "#FFFFFF" text= "#000000" link= "#FF0000" vlink= "#800000" alink= "#FF00FF" background= "?" >
<?php
How do you split the result into pages and like ' limits ' in MySQL?
// ===========================================================
Tutorial by Neil Craig (neilc@netactive.co.za)
date:2001-06-05
With this example, I'll explain paging of database queries where the
The than the developer want to the page, but wish to
Split the result into seperate pages.
The table "sample_table" accessed in this tutorial has 4 fields:
pk_id, FIELD1, FIELD2 and FIELD3. The types don ' t matter but you should
Define a primary key on the pk_id field.
$display _rows = 5; The rows that should is display at a time. You can
Modify this if you are like.
Connect to the Oracle database
Putenv ("Oracle_sid=purk");
Putenv ("oracle_home=/export/oracle8i");
Putenv ("tns_admin= $ORACLE _home/network/admin");
$OracleDBConn = Ocilogon ("Purk", "Purk", "Lengana.world");
This query counts the records
$sql _count = "SELECT count (*) from sample_table";
Parse the SQL string & Execute it
$row _count=ociparse ($OracleDBConn, $sql _count);
Ociexecute ($row _count);
From the parsed & executed query, we get the amount of records found.
I ' m not storing this result into a session variable because it allows for
New records to being shown as it is entered by another user while the
is printed.
if (Ocifetch ($row _count)) {
$num _rows = Ociresult ($row _count,1);
} else {
$num _rows = 0; If no record is found
}
Free the "resources" were used for this query
Ocifreestatement ($row _count);
We need to prepare this query that'll print the results as a page. I'll
Explain the query to your in detail.
If no page is specified in the URL (ex. http://mysite.com/result.php?page=2),
Set it to page 1.
if (Empty ($page) | | | $page = = 0) {
$page = 1;
}
The start range from where the results should is printed
$start _range = (($page-1) * $display _rows) + 1;
The end range to where the results should is printed
$end _range = $page * $display _rows;
The main query. It consists of 3 "select" statements nested into each
Other. The center query is the query you would normally the
Records you want. Do, ordering and "WHERE" clauses in this statement.
We Select the rows to limit our results but because the row numbers are
Assigned to the "rows before any ordering are done, lets the code print the
Result unsorted.
The second nested "SELECTED" assigns the new row numbers to the result
For us to select from.
$sql = "Select pk_id, FIELD1, FIELD2, FIELD3, row_no from (select pk_id,";
$sql. = "FIELD1, FIELD2, FIELD3, RowNum row_no from (SELECT pk_id, FIELD1,";
$sql. = "FIELD2, FIELD3 from Sample_table Order by FIELD3)" WHERE row_no BETWEEN ";
$sql. = $start _range. " and ". $end _range;
Start results formatting
echo "<table width= ' border= ' 1 ' cellspacing= ' 1 ' cellpadding= ' 2 ' align= ' center ' >";
echo "<tr bgcolor= ' #666666 ' >";
echo "<td><b><font color= ' #FFFFFF ' >pk id</font></b></td>";
echo "<td><b><font color= ' #FFFFFF ' >field 1</font></b></td>";
echo "<td><b><font color= ' #FFFFFF ' >field 2</font></b></td>";
echo "<td><b><font color= ' #FFFFFF ' >field 3</font></b></td>";
echo "<td><b><font color= ' #FFFFFF ' >row no</font></b></td>";
echo "</tr>";
if ($num _rows!= 0) {
Parse the SQL string & Execute it
$rs =ociparse ($OracleDBConn, $sql);
Ociexecute ($RS);
Get number of columns to use later
$num _columns = Ocinumcols ($RS);
while (Ocifetch ($rs)) {
echo "<tr>";
for ($i = 1; $i < ($num _columns + 1); $i + +) {
$column _value = Ociresult ($rs, $i);
echo "<TD> $column _value</td>";
}
echo "</tr>";
}
} else {
Print a message stating this no records was found
echo "<tr><td align=center>sorry! No Records was found</td></tr> ";
}
Close the table
echo "</TABLE>";
Free resources and close connection
Ocifreestatement ($RS);
Ocilogoff ($OracleDBConn);
?>
<div align=center>
<?php
Here we'll print the links to the other pages
Calculating the amount of pages
if ($num _rows% $display _rows = 0) {
$total _pages = $num _rows/$display _rows;
} else {
$total _pages = ($num _rows/$display _rows) + 1;
Settype ($total _pages, Integer); Rounding the variable
}
If This isn't the the "the" the "the" the "page" a link to the previous
if ($page!= 1) {
echo "<a href= '". $PHP _self. "? Page= ". ($page-1). "' >Previous</a> ";
}
Now we can print the links to the other pages
for ($i = 1; $i <= $total _pages; $i + +) {
if ($page = = $i) {
Don ' t print the link to the current page
echo "". $i;
} else {
Print the links to the other pages
echo "<a href= '". $PHP _self. "? Page= ". $i." ' > ". $i." </a> ";
}
}
If This isn't the last page print a link to the next page
if ($page < $total _pages) {
echo "<a href= '". $PHP _self. "? Page= ". ($page + 1). "' >Next</a> ";
}
?>
</div>
<?php
I ' m just adding this section to print some of the variables for extra info
and some debugging
echo "<p><b>total pages: </b>". $total _pages. " </p> ";
echo "<p><b>number of records: </b>". $num _rows. " </p> ";
echo "<p><b>the SQL Query is:</b>". $sql. " </p> ";
?>
</BODY>
</HTML>
