Detailed description of pagination with PHP MySQL (i)

Pagination Display Detailed


1. Preface

Pagination display is an uncommon 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 is not a clue, so specially to write this article on the specific explanation of this issue, 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 as an example, if you want to intercept a piece of content from the table, SQL statement 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, 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 results of each page, plus a
}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
}