PHP pagination Display production detailed explanation

pagination | Show 1, preface

Paging display is a very common way to browse and display large amounts of data, one of the most frequently handled events in Web programming. For the veteran of Web programming, writing this code is as natural as breathing, but for beginners, often on this issue does not feel a clue, so specially to write this article on this issue for detailed explanation, and strive to let the reading of this article after reading the principle of pagination and the realization of the method to understand. This article is for beginners to read, and all sample code is written using PHP.

2. Principle

The so-called paging display, that is, the result set in the database for the artificial section of the display, here need two initial parameters:

How many records per page ($PageSize)?
What is the current page ($CurrentPageID)?

Now just give me one more result set, and I can show a specific piece of results.
As for other parameters, such as: the previous page ($PreviousPageID), the next page ($NextPageID), the total number of pages ($numPages), etc., can be based on these things in front.
Take the MySQL database for example, if you want to intercept a piece of content from a table, SQL statements can be used: SELECT * FROM table limit offset, rows. Take a look at the following set of SQL statements and try to find the gauge in them.

First 10 records: SELECT * FROM table limit 0,10
11th to 20th record: SELECT * FROM table limit 10,10
21st to 30th Record: SELECT * FROM table limit 20,10
......

This set of SQL statements is actually a SQL statement that takes every page of data from the table when $pagesize=10, and we can summarize this template:

SELECT * FROM table limit ($CurrentPageID-1) * $PageSize, $PageSize

Take this template to the corresponding value and the top of the group of SQL statements in contrast to see if that is the case. The most important thing to do is to get the data, the rest is just passing the parameters, constructing the appropriate SQL statements and then using PHP to get the data from the database and display it. I'll explain this in concrete code.

3. Simple code
Please read the following code in detail, debug your own run once, it is best to modify it once, plus its own functions, such as search and so on.

<?php
Establishing a database connection
$link = mysql_connect ("localhost", "Mysql_user", "Mysql_password")
Or Die ("Could not connect:".) Mysql_error ());
Get the current number of pages
if (Isset ($_get[' page ')) {
$page = intval ($_get[' page '));
}
else{
$page = 1;
}
Number per page
$PageSize = 10;
Get total amount of data
$sql = "SELECT count (*) as amount from table";
$result = mysql_query ($sql);
$row = Mysql_fetch_row ($result);
$amount = $row [' Amount '];
Count how many pages there are in total
if ($amount) {
if ($amount < $page _size) {$page _count = 1;}///If the total amount of data is less than $pagesize, then only one page
if ($amount% $page _size) {//Total data amount divided by the remainder of each page
$page _count = (int) ($amount/$page _size) + 1; If there are more than one, then the number of pages equals the total amount of data divided by the result of each page.
}else{
$page _count = $amount/$page _size; If there is no remainder, the number of pages equals the total amount of data divided by the results per page
}
}
else{
$page _count = 0;
}

Page links
$page _string = ';
if ($page = = 1) {
$page _string. = ' first page | previous page | ';
}
else{
$page _string. = ' <a href=?page=1> first page </a>|<a href=?page= '. ($page-1). ' > on a page </a>| ';
}
if ($page = = $page _count) | | ($page _count = 0)) {
$page _string. = ' next page | last ';
}
else{
$page _string. = ' <a href=?page= '. ($page + 1). ' > next page </a>|<a href=?page= '. $page _count. ' > Last </a> ';
}
Gets the data and returns the result in a 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 ();
}
There is no code to display the results, which is not in the scope of the discussion, as long as a foreach can be used to display the result with a two-dimensional array.
?>

4, OO style code
The database connection in the following code is handled using the Pear DB Class

<?php
FileName:Pager.class.php
Pagination class, which is used only to work with data structures, and is not responsible for working with the display
Class Pager
{
var $PageSize; Number of pages per page
var $CurrentPageID; Current number of pages
var $NextPageID; Next page
var $PreviousPageID; Previous page
var $numPages; Total pages
var $numItems; Total number of records
var $isFirstPage; is the first page
var $isLastPage; is the last page
var $sql; SQL query Statement

function Pager ($option)
{
Global $db;
$this->_setoptions ($option);
Total number of articles
if (!isset ($this->numitems))
{
$res = $db->query ($this->sql);
$this->numitems = $res->numrows ();
}
Total 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;
}

/***
*
* Returns the database connection of the result set
* You can use this method to get the database connection directly when the result set is larger, and then traverse outside the class, which is less expensive
* If the result set is not very large, you can get the result of the two-dimensional array format directly using the Getpagedata method
* Getpagedata method is also called 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); Using the Pear Db::limitquery method to guarantee database compatibility

return $link;
}
Else
{
return false;
}
}

/***
*
* Returns the result set in a 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;
}
}
?>
<?php
FileName:test_pager.php
This is a simple example code that omits the code to establish a database connection using the Pear DB Class
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 = "Home | prev |";
}
Else
{
$turnover = "<a href= ' page=1&numitems=". $pager->numitems. "' > Home </a>|<a href= '? page= '. $pager->previouspageid. " &numitems= ". $pager->numitems." ' > On a page </a>| ";
}
if ($pager->islastpage)
{
$turnover. = "Next page | last";
}
Else
{
$turnover. = "<a href= ' page=". $pager->nextpageid. " &numitems= ". $pager->numitems." ' > next page </a>|<a href= '? page= '. $pager->numpages. " &numitems= ". $pager->numitems." ' > Last </a> ";
}
?>


There are two places to note:

This class simply handles the data and is not responsible for the display, because I think it's a bit grudging to put the data processing and the display of the results in a class. Display when the situation and requirements vary, rather than their own according to the results of the class to deal with, a better way is based on this pager class to inherit a subclass of their own to display different pagination, such as Display user page list can:

<?php
Class Memberpager extends Pager
{
function Showmemberlist ()
{
Global $db;

$data = $this->getpagedata ();
Code to display results
// ......
}
}
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 ();
?>


The second place to be explained is the compatibility of different databases, and the way in which a result is intercepted in different databases is not the same.
Mysql:select * FROM table limit offset, rows
Pgsql:select * FROM table limit m offset n
......
So you need to use the Limitquery method of the Pear DB class to get results inside the class.

OK, write the work, hope to spend time reading these words you do not think it is a waste of time.