- Select * from table limit 0, 10 // The first 10 records
- Select * from table limit 10, 10 // 11th to 20 records
- Select * from table limit 20, 10 // 21st to 30 records
- ......
-
This set of SQL statements is the SQL statement used to retrieve data of each page in the table when $ pagesize = 10. we can summarize this template: select * from table limit ($ currentpageid-1) * $ pagesize, $ pagesize takes this template into the corresponding value and compares it with the preceding SQL statement to see if this is the case. After solving the most important problem of how to obtain data, the rest is simply passing parameters, constructing appropriate SQL statements, and then using php to obtain data from the database and display it. Php paging code:
- // Establish a database connection
- $ Link = mysql_connect ("localhost", "mysql_user", "mysql_password ")
- Or die ("cocould not connect:". mysql_error ());
- // Obtain the current page number
- If (isset ($ _ get ['Page']) {
- $ Page = intval ($ _ get ['Page']);
- }
- Else {
- $ Page = 1;
- }
- // Number of pages per page
- $ Pagesize = 10;
- // Obtain the total data volume
- $ SQL = "select count (*) as amount from table ";
- $ Result = mysql_query ($ SQL );
- $ Row = mysql_fetch_row ($ result );
- $ Amount = $ row ['amount'];
- // Count the total number of pages
- If ($ amount ){
- If ($ amount <$ page_size) {$ page_count = 1;} // if the total data volume is less than $ pagesize, there is only one page
- If ($ amount % $ page_size) {// Retrieve the total data volume divided by the remainder of each page
- $ Page_count = (int) ($ amount/$ page_size) + 1; // if there is a remainder, the number of pages equals the total data volume divided by the result of each page number and then adds one
- } Else {
- $ Page_count = $ amount/$ page_size; // if there is no remainder, the number of pages equals the total data volume divided by the result of each page
- }
- }
- Else {
- $ Page_count = 0;
- }
-
- // Flip link
- $ Page_string = '';
- If ($ page = 1 ){
- $ Page_string. = 'Page 1 | previous page | ';
- }
- Else {
- $ Page_string. = '"; first page | previous page | ';
- }
- If ($ page = $ page_count) | ($ page_count = 0 )){
- $ Page_string. = 'next page | last page ';
- }
- Else {
- $ Page_string. = 'next page | last page ';
- }
- // Obtain data and return results in two-dimensional array format
- If ($ amount ){
- $ SQL = "select * from table order by id desc limit". ($ page-1) * $ page_size. ", $ page_size ";
- $ Result = mysql_query ($ SQL );
-
- While ($ row = mysql_fetch_row ($ result )){
- $ Rowset [] = $ row;
- }
- } Else {
- $ Rowset = array ();
- }
- // The code that does not contain the display result is not in the scope of the discussion. you only need to use foreach to easily display the result using the two-dimensional array.
- ?>
-
Oo-style code. the database connection in the following code is processed using the pear db class.
- // Filename: pager. class. php
- // Paging class. this class is only used to process the data structure and is not used to process the display.
- Class pager
- {
- Var $ pagesize; // Number of pages per page
- Var $ currentpageid; // Current page number
- Var $ nextpageid; // Next page
- Var $ previouspageid; // Previous Page
- Var $ numpages; // The total number of pages.
- Var $ numitems; // The total number of records
- Var $ isfirstpage; // whether the first page is displayed
- Var $ islastpage; // whether it is the last page
- Var $ SQL; // SQL query statement
-
- Function pager ($ option)
- {
- Global $ db;
- $ This-> _ setoptions ($ option );
- // Total number of entries
- If (! Isset ($ this-> numitems ))
- {
- $ Res = $ db-> query ($ this-> SQL );
- $ This-> numitems = $ res-> numrows ();
- }
- // Total number of pages
- If ($ this-> numitems> 0)
- {
- If ($ this-> numitems <$ this-> pagesize) {$ this-> numpages = 1 ;}
- If ($ this-> numitems % $ this-> pagesize)
- {
- $ This-> numpages = (int) ($ this-> numitems/$ this-> pagesize) + 1;
- }
- Else
- {
- $ This-> numpages = $ this-> numitems/$ this-> pagesize;
- }
- }
- Else
- {
- $ This-> numpages = 0;
- }
-
- Switch ($ this-> currentpageid)
- {
- Case $ this-> numpages = 1:
- $ This-> isfirstpage = true;
- $ This-> islastpage = true;
- Break;
- Case 1:
- $ This-> isfirstpage = true;
- $ This-> islastpage = false;
- Break;
- Case $ this-> numpages:
- $ This-> isfirstpage = false;
- $ This-> islastpage = true;
- Break;
- Default:
- $ This-> isfirstpage = false;
- $ This-> islastpage = false;
- }
-
- If ($ this-> numpages> 1)
- {
- If (! $ This-> islastpage) {$ this-> nextpageid = $ this-> currentpageid + 1 ;}
- If (! $ This-> isfirstpage) {$ this-> previouspageid = $ this-> currentpageid-1 ;}
- }
-
- Return true;
- }
-
- /***
- *
- * Database connection to the returned result set
- * When the result set is large, you can directly use this method to obtain the database connection and traverse the database outside of the class. this reduces the overhead.
- * If the result set is not large, you can directly use getpagedata to obtain results in two-dimensional array format.
- * The getpagedata method also calls this method to obtain the result.
- *
- ***/
-
- Function getdatalink ()
- {
- If ($ this-> numitems)
- {
- Global $ db;
-
- $ Pageid = $ this-> currentpageid;
-
- $ From = ($ pageid-1) * $ this-> pagesize;
- $ Count = $ this-> pagesize;
- $ Link = $ db-> limitquery ($ this-> SQL, $ from, $ count); // use the pear db: limitquery method to ensure database compatibility
-
- Return $ link;
- }
- Else
- {
- Return false;
- }
- }
-
- /***
- *
- * Returns the result set in two-dimensional array format.
- *
- ***/
-
- Function getpagedata ()
- {
- If ($ this-> numitems)
- {
- If ($ res = $ this-> getdatalink ())
- {
- If ($ res-> numrows ())
- {
- While ($ row = $ res-> fetchrow ())
- {
- $ Result [] = $ row;
- }
- }
- Else
- {
- $ Result = array ();
- }
-
- Return $ result;
- }
- Else
- {
- Return false;
- }
- }
- Else
- {
- Return false;
- }
- }
-
- Function _ setoptions ($ option)
- {
- $ Allow_options = array (
- 'Pagesize ',
- 'Currentpageid ',
- 'SQL ',
- 'Numitems'
- );
-
- Foreach ($ option as $ key => $ value)
- {
- If (in_array ($ key, $ allow_options) & ($ value! = Null ))
- {
- $ This-> $ key = $ value;
- }
- }
-
- Return true;
- }
- }
- ?>
Paging code call:
- // Filename: test_pager.php
- // This is a simple sample code. the code for establishing a database connection using the pear db class is omitted in the front.
- Require "pager. class. php ";
- If (isset ($ _ get ['Page'])
- {
- $ Page = (int) $ _ get ['Page'];
- }
- Else
- {
- $ Page = 1;
- }
- $ SQL = "select * from table order by id ";
- $ Pager_option = array (
- "SQL" => $ SQL,
- "Pagesize" => 10,
- "Currentpageid" => $ page
- );
- If (isset ($ _ get ['numitems '])
- {
- $ Pager_option ['numitems '] = (int) $ _ get ['numitems'];
- }
- $ Pager = @ new pager ($ pager_option );
- $ Data = $ pager-> getpagedata ();
- If ($ pager-> isfirstpage)
- {
- $ Turnover = "homepage | previous page | ";
- }
- Else
- {
- $ Turnover = "numitems." '> homepage | previouspageid. "& numitems =". $ pager-> numitems. "'> Previous Page | ";
- }
- If ($ pager-> islastpage)
- {
- $ Turnover. = "next page | last page ";
- }
- Else
- {
- $ Turnover. = "nextpageid. "& numitems = ". $ pager-> numitems. "'> Next Page | numpages. "& numitems = ". $ pager-> numitems. "'> last page ";
- }
- ?>
Note: This class only processes data and is not responsible for displaying the data, because it is barely possible to put the data processing and result display in a class. When the display status and requirements change, it is better to handle the problem based on the results given by the class. the better way is to display different pages based on the pager class inheriting a subclass of its own, for example, to display the user page list, you can:
- Class memberpager extends pager
- {
- Function showmemberlist ()
- {
- Global $ db;
-
- $ Data = $ this-> getpagedata ();
- // Code for displaying the result
- //......
- }
- }
- /// Call
- If (isset ($ _ get ['Page'])
- {
- $ Page = (int) $ _ get ['Page'];
- }
- Else
- {
- $ Page = 1;
- }
- $ SQL = "select * from members order by id ";
- $ Pager_option = array (
- "SQL" => $ SQL,
- "Pagesize" => 10,
- "Currentpageid" => $ page
- );
- If (isset ($ _ get ['numitems '])
- {
- $ Pager_option ['numitems '] = (int) $ _ get ['numitems'];
- }
- $ Pager = @ new memberpager ($ pager_option );
- $ Pager-> showmemberlist ();
- ?>
Second, the compatibility of different databases may vary in the way a piece of results are intercepted in different databases.
- $ Pagenum = @ ceil ($ num/$ pagesize );
- If ($ num> $ pagesize ){
- If ($ pageval <= 1) $ pageval = 1;
- If ($ pageval ==$ pagenum ){
- Echo "previous page next page last page ";
- // Echo "no.". $ pageval. "page/total". $ pagenum. "page ";
- Echo "to\ N ";For ($ I = 1; $ I <= $ pagenum; $ I ++ ){If ($ I = $ pageval)Echo"$ I\ N ";ElseEcho"$ I\ N ";}Echo"Page, total $ pagenum page ";
- }
- }
- }
- ?>
|