Make pagination

In a real project, we write the host, user name, password, and library in the configuration file.
If it is written hard in the code, if the relevant information of the database server changes, it is obviously not in line with the programmer's thinking to modify all the code.

In addition, in every page that needs to connect to the database. We all need to write connections, judge errors, and set character sets, which is too troublesome. And it is not conducive to reusing these codes.

We can use the include series of functions mentioned before to achieve our goal. The example picture is as follows:

# Therefore, we can make a configuration file config.php. Set all the configurations that need to be used as constants. The code is as follows:

<?php
//数据库服务器
define('DB_HOST', 'localhost');
//数据库用户名
define('DB_USER', 'root');
//数据库密码
define('DB_PWD', 123456789);
//库名
define('DB_NAME', 'book');
//字符集
define('DB_CHARSET', 'utf8');
?>

We will extract the connection.php page. When we need to connect to the database in the future, we only need to include the connection.php file. The code is as follows:

<?php
include 'config.php';
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PWD, DB_NAME);
if (mysqli_errno($conn)) {
    mysqli_error($conn);
    exit;
}
mysqli_set_charset($conn, DB_CHARSET);

We can realize database connection by directly including the connection.php file in each file in the future:

include 'connection.php';

Complete the above preparations, and then complete the paging. The paging effect is as follows:

The page must contain the following basic elements:

When we control page numbers, we implement page number control by passing in the page number value in the URL address bar. By appending the page number-related information to page.php, we can calculate more effective information. The effect of url controlling paging is as follows:

In the code implementation, these two values ​​​​are actually realized through the offset (offset) and quantity (num) after limit. of paging.

limit offset , num

Assume that each page displays 5 items. The final paging control limit formula is as follows:

The value of offset is (n-1)*5
num is the specified 5

We implement the business through code:

1. Calculate the parameters required for paging

Total number

Get the total number $count by querying the count(id) of the user table.

$count_sql = 'select count(id) as c from user';
$result = mysqli_query($conn, $count_sql);
$data = mysqli_fetch_assoc($result);
//得到总的用户数
$count = $data['c'];

Current page

When you first enter the page.php page, the url is http://www.phpxy.com/page.php, followed by the page identification number that does not exist?page=1 of.

So we need to manually create a page identification number and pass it to the current page number variable $page.

We are afraid that there are decimals etc. in the page passed by the user, so we do a forced type conversion: (int) $_GET['page'].

The first way of writing:

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;

The second way of writing

if (isset($_GET['page'])) {
    $page = (int) $_GET['page'];
} else {
    $page = 1;
}

Last page

Each page must be an integer. Just like math in elementary school. On average, 5.6 people should prepare how many apples. The answer must be 6.

If the page comes out with 20.3 pages, the rounding function ceil must be used. Let the number of pagination become 21.

We divide the total number by the number of data displayed on each page to get the total number of pages.

//Display number per page

$num = 5;
$total = ceil($count / $num);

Abnormal control of previous page and next page

If the user clicks on the previous page on the first page, on the last page What should I do if I click on the next page?

In this case, the data will exceed the range, causing no data to be displayed when we paginate.

Obviously this unusual situation needs to be taken into account. Therefore, if the first page is subtracted by one during paging, we make it the first page.
When adding one to the last page, we make it the last page, that is, the exception control is completed.

if ($page <= 1) {
    $page = 1;
} 
if ($page >= $total) {
    $page = $total;
}

2. SQL statement

We said before that the core of paging is to control the number of displays on each page through the offset and num in the SQL statement.

We also listed the specific formula above. We convert the company into code as follows:

$num = 5;
$offset = ($page - 1) * $num;

We apply $num and $offset to the SQL statement:

$sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num";

Control the paging value in the URI

echo '<tr>
    <td colspan="5">
    <a href="page.php?page=1">首页</a>
    <a href="page.php?page=' . ($page - 1) . '">上一页</a>
    <a href="page.php?page=' . ($page + 1) . '">下一页</a>
    <a href="page.php?page=' . $total . '">尾页</a>
    当前是第 ' . $page . '页  共' . $total . '页
    </td>
    </tr>';

We finally connect the entire business together to achieve the final effect. The code is as follows:

<?php
include 'connection.php';
$count_sql = 'select count(id) as c from user';
$result = mysqli_query($conn, $count_sql); 
$data = mysqli_fetch_assoc($result);
//得到总的用户数
$count = $data['c'];
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
/*
if (isset($_GET['page'])) {
    $page = (int) $_GET['page'];
} else {
    $page = 1;
}
 */
//每页显示数
$num = 5;
//得到总页数
$total = ceil($count / $num);
if ($page <= 1) {
    $page = 1;
}
if ($page >= $total) {
    $page = $total;
}
$offset = ($page - 1) * $num;
$sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num";
$result = mysqli_query($conn, $sql);
if ($result && mysqli_num_rows($result)) {
    //存在数据则循环将数据显示出来
    echo '<table width="800" border="1">';
    while ($row = mysqli_fetch_assoc($result)) {
        echo '<tr>';
        echo '<td>' . $row['username'] . '</td>';
        echo '<td>' . date('Y-m-d H:i:s', $row['createtime']) . '</td>';
        echo '<td>' . long2ip($row['createip']) . '</td>';
        echo '<td><a href="edit.php?id=' . $row['id'] . '">编辑用户</a></td>';
        echo '<td><a href="delete.php?id=' . $row['id'] . '">删除用户</a></td>';
        echo '</tr>';
    }
    echo '<tr><td colspan="5"><a href="page.php?page=1">首页</a>  <a href="page.php?page=' . ($page - 1) . '">上一页</a>   <a href="page.php?page=' . ($page + 1) . '">下一页</a>  <a href="page.php?page=' . $total . '">尾页</a>  当前是第 ' . $page . '页  共' . $total . '页 </td></tr>';
    echo '</table>';
} else {
    echo '没有数据';
}
mysqli_close($conn);
?>

Paging principle

1. First understand the usage of limit in SQL statements

SELECT * FROM table... limit starting position, number of operations (where the start The position starts from 0)

Example:

Get the first 20 records: SELECT * FROM table... limit 0, 20
Get the 20 records starting from the 11th one: SELECT * FROM table …… limit 10 , 20

LIMIT n is equivalent to LIMIT 0,n.

Such as select * from table LIMIT 5; //Return the first 5 rows, the same as select * from table LIMIT 0, 5

2. Paging principle

The so-called paging display means that the result set in the database is displayed segment by segment. Page)

The first 10 records: select * from table limit 0,10

The 11th to 20th records: select * from table limit 10,10

The 21st to 30th records ：select * from table limit 20,10

Paging formula:

(current page number - 1) X number of items per page, number of items per page

Select * from table limit ($Page- 1) * $PageSize, $PageSize

3. $_SERVER["REQUEST_URI"] function is a type of predefined server variables. All those starting with $_SERVER are called predetermined server variables.

The function of REQUEST_URI is to obtain the current URI, which is the complete address path except the domain name.

example:

The current page is: http://www.test.com/home.php?id=23&cid=22

echo $_SERVER["REQUEST_URI"]

The result is: / home.php?id=23&cid=22

4. parse_url() parsing URL function

parse_url() is to parse the URL into a Function of array with fixed key value

Example

$ua=parse_url("http://username:password@hostname/path?arg=value#anchor");
 print_r($ua);

Result:

Array
(
[scheme] => http ;Protocol
                          ; /PATH; Path

[Query] = & GT; ARG = Value; take the parameter

[fragment] = & gt; 5. Code example

This message paging is divided into three parts, one is the database design, one is the connection page, and the other is the display page.

(1) Design database The design database is named bbs. There is a data table called message, which contains fields such as title, lastdate, user, and content, respectively. Message title, message date, message person, message content

(2) Connection page

<?php
$conn = @ mysql_connect("localhost", "root", "123456") or die("数据库链接错误"); mysql_select_db("bbs", $conn); mysql_query("set names 'GBK'"); //使用GBK中文编码;
//将空格，换行转换为HTML可解析
function htmtocode($content) {  $content = str_replace("\n", "<br>", str_replace(" ", "&nbsp;", $content)); //两个str_replace嵌套
 return $content; }
//$content=str_replace("'","‘",$content);  //htmlspecialchars(); 
?>

(3) Display page

<?php  
include("conn.php");
$pagesize=2;       
//设置每页显示2个记录 $url=$_SERVER["REQUEST_URI"];   
$url=parse_url($url); 
$url=$url[path];
$numq=mysql_query("SELECT * FROM `message`"); 
$num = mysql_num_rows($numq);
if($_GET[page]){ $pageval=$_GET[page]; 
$page=($pageval-1)*$pagesize; 
$page.=','; 
} 
if($num > $pagesize)
{ 
if($pageval<=1)$pageval=1;
 echo "共 $num 条".  " <a href=$url?page=".($pageval-1).">上一页</a> 
 <a href=$url?page=".($pageval+1).">下一页</a>"; 
 } 
 $SQL="SELECT * FROM `message` limit $page $pagesize ";    
  $query=mysql_query($SQL);   
  while($row=mysql_fetch_array($query)){ 
  ?>

rrree

Next Section