An ORACLE paging program is very practical.

An ORACLE paging program is very practical.


Paging Test








// How to split the result into pages, like 'limit' in MySQL?
// ================================================ ==================================
// Tutorial by Neil Craig (neilc@netactive.co.za)
// Date: 2001-06-05
// With this example, I will explain paging of database queries where
// Result is more than the developer want to print to the page, but wish
// 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 shoshould
// Define a primary key on the PK_ID field.

$ Display_rows = 5; // The rows that shoshould be displayed at a time. You can
// Modify this if you 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
// New records to be shown as it is entered by another user while the result
// Is printed.
If (OCIFetch ($ row_count )){
$ Num_rows = OCIResult ($ row_count, 1 );
} Else {
$ Num_rows = 0; // If no record was found
}

// Free the resources that were used for this query
OCIFreeStatement ($ row_count );

// We need to prepare the query that will print the results as a page. I will
// Explain the query to you in detail.

// If no page was 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 shoshould be printed
$ Start_range = ($ page-1) * $ display_rows) + 1;

// The end range to where the results shocould be 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 wocould normally use to return
// Records you want. Do you ordering and "WHERE" clses in this statement.
// We select the rows to limit our results but because the row numbers are
// Assigned to the rows before any ordering is done, lets the code print
// 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 ";
$ SQL. = $ start_range. "AND". $ end_range;

// Start results formatting
Echo"

































";Echo" ";Echo" ";Echo" ";Echo" ";Echo" ";Echo" ";Echo" ";If ($ num_rows! = 0 ){// Parse the SQL string & execute it$ Rs = OCIParse ($ OracleDBConn, $ SQL );OCIExecute ($ rs );// Get number of columns for use later$ Num_columns = OCINumCols ($ rs );While (OCIFetch ($ rs )){Echo" ";For ($ I = 1; $ I <($ num_columns + 1); $ I ++ ){$ Column_value = OCIResult ($ rs, $ I );Echo" ";}Echo" ";}} Else {// Print a message stating that no records was foundEcho" ";}// Close the tableEcho"

PK ID	Field 1	Field 2	Field 3	Row No
$ Column_value
Sorry! No records was found

";

// Free resources and close connection
OCIFreeStatement ($ rs );
OCILogoff ($ OracleDBConn );

?>

// Here we will 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 is not the first page print a link to the previous page
If ($ page! = 1 ){
Echo "Previous ";
}

// 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 "". $ I ."";
}
}

// If this is not the last page print a link to the next page
If ($ page <$ total_pages ){
Echo "Next ";
}

?>

// I'm just adding this section to print some of the variables for extra info
// And some debugging

Echo"

Total pages:". $ Total_pages ."

";
Echo"

Number of records:". $ Num_rows ."

";
Echo"

The SQL Query is:". $ SQL ."

";

?>