<Body bgcolor = "# ffffff" text = "#000000" link = "# ff0000" vlink = "#800000" alink = "# ff00ff" background = "? ">
<? PHP
// 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 "<Table width = '000000' border = '1' cellspacing = '1' cellpadding = '2' align = 'center'> ";
Echo "<tr bgcolor = '#666666'> ";
Echo "<TD> <B> <font color = '# ffff'> PK id </font> </B> </TD> ";
Echo "<TD> <B> <font color = '# ffff'> Field 1 </font> </B> </TD> ";
Echo "<TD> <B> <font color = '# ffff'> Field 2 </font> </B> </TD> ";
Echo "<TD> <B> <font color = '# ffff'> Field 3 </font> </B> </TD> ";
Echo "<TD> <B> <font color = '# ffff'> 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 for 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 that 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 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 "<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 is not 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>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
