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', 'secret');
//库名
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:
<?phpinclude '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:
Element | Description | Remark |
---|
#Homepage | First page to enter The first page | defaults to 1 when passing parameters via get |
Previous page | Current page minus 1 | If the page number is the first page, subtract 1, it should be the first page |
Next page | Add 1 to the current page | If it is Last |
Last page | Last page | The total number of items is divided by the number of displays per page to get the total number of pages |
Current page | The current page number | is the current page number |
Total number of pages | total How many pages are there | The total number of items divided by the number of displays per page |
When we control the page number, 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
Page number | get value in url | limit offset, quantity |
---|
Page 1 | 1 | 0, 5 |
Page 2 | 2 | 5,5 |
Page 3 | 3 | 10,5 |
Page n | n | (n-1)*5,5 |
Assume that each page displays 5 items. The final control limit formula for paging is as follows:
offset的值为 (n-1)*5
num 为规定的5
We implement the business through code:
1. Calculate the parameters required for paging
Total number
通过查询user表的count(id),得到总数$count。
$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.php.com/page.php, followed by no There is ?page=1 page identification number.
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 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.
//每页显示数
$num = 5;
$total = ceil($count / $num);
Previous page and next page abnormal situation control
What if the user clicks the previous page on the first page and clicks the next page on the last 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 display of each page through the offset and num in the SQL statement. number.
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:
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);
Next Section<?php
$num = 5;
$offset = ($page - 1) * $num;
//我们将$num和$offset应用于SQL语句中:
$sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num";
//控制好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>';
//我们最后将整体业务串联起来实现最终效果,代码如下:
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);
?>
- Chapter1Why choose this course to learn PHP
- Why learn PHP?
- What is PHP
- You can learn even with z...
- Why can't some people lea...
- Chapter2PHP environment installation
- What is the development e...
- Windows environment insta...
- Linux environment install...
- Other development environ...
- Tool selection for writin...
- Chapter3php basic syntax
- PHP basic syntax
- Our first piece of PHP co...
- Variables in php - you wi...
- echo display command
- Learning php annotations
- Data types are not myster...
- PHP integer type is an in...
- PHP data type Boolean (ac...
- PHP data type string
- PHP data type floating po...
- PHP flow control if else ...
- PHP data type NULL type
- php data type array
- Resource type of php data...
- PHP data type viewing and...
- Automatic conversion and ...
- Object (will learn later)
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- Variable references for P...
- PHP basic syntax arithmet...
- PHP basic syntax assignme...
- PHP basic syntax: self-in...
- PHP basic syntax comparis...
- Logical operations of php...
- PHP basic syntax bit oper...
- PHP basic syntax: ternary...
- Chapter4PHP process control
- Process control in PHP
- PHP process control if co...
- PHP flow control if state...
- Nested if...else...elseif...
- Multiple nesting of if st...
- Use of branch structure s...
- Use of loop statements in...
- while loop
- The difference between do...
- PHP flow control for loop...
- PHP flow control goto syn...
- Chapter5Basic function syntax of PHP
- Basic function syntax of ...
- PHP function basic syntax...
- PHP custom function callb...
- PHP custom function varia...
- PHP custom function anony...
- Internal function of php ...
- Variable scope of php cus...
- Reference to parameters o...
- PHP custom function recur...
- Static variables of php c...
- php uses system built-in ...
- php file contains functio...
- PHP math commonly used fu...
- PHP function to obtain pe...
- php date validation funct...
- PHP gets localized timest...
- PHP program execution tim...
- PHP string common functio...
- Chapter6PHP arrays and data structures
- PHP arrays and data struc...
- php array definition
- PHP array calculation
- php for loop traverses in...
- php foreach traverses as...
- PHP list, each function t...
- PHP commonly used array m...
- Common functions for php ...
- Chapter7Regular expressions in PHP
- Regular expressions in PH...
- Delimiter expressed by ph...
- Atoms in php regular expr...
- Metacharacters in php reg...
- Pattern modifiers in php ...
- Tips and commonly used re...
- PHP uses regular expressi...
- Chapter8php file system
- File system
- php read file
- php creates and modifies ...
- php creates temporary fil...
- php move, copy and delete...
- php detect file attribute...
- Common functions and cons...
- php file locking mechanis...
- php directory processing ...
- php file permission setti...
- php file path function
- PHP implements file guest...
- PHP implementation exampl...
- Chapter9PHP file upload
- PHP file upload
- When uploading files, you...
- Steps to upload php files
- Precautions for php file ...
- php completes file upload...
- php multiple file upload
- PHP file upload progress ...
- Chapter10PHP image processing
- PHP image processing
- PHP image processing gd2 ...
- PHP uses image processing...
- PHP development verificat...
- php image scaling and cro...
- PHP image watermark proce...
- Chapter11PHP error handling
- Error handling
- PHP error handling prohib...
- PHP error handling error ...
- PHP error handling error ...
- PHP error handling custom...
- Chapter12Getting started with MySQL
- Getting Started with MySQ...
- Mysql database introducti...
- Mysql entertainment expla...
- mysql database installati...
- Data statement operation ...
- Mysql connect to database
- Mysql database operation
- Mysql data table operatio...
- Mysql data field operatio...
- Mysql data type
- Mysql character set
- Mysql table engine
- Mysql index
- Mysql add, delete, modify...
- Mysql add, delete, modify...
- Mysql multi-table joint q...
- Mysql addition, deletion,...
- Mysql add, delete, modify...
- DCL statement
- Learn commonly used Engli...
- Chapter13PHP operates mysql database
- PHP operates mysql databa...
- PHP database connection s...
- PHP operates the database...
- PHP database operation: m...
- PHP database operation: p...
- PHP database operation: b...
- PHP database operation to...
- The ultimate solution to ...
- Chapter14php session management and control
- session overview
- Overview of Cookies for P...
- php session control Cooki...
- PHP session control using...
- php SESSION application e...
- Session management and co...
- Chapter15Making a thief program through cURL
- php curl usage methods an...
- php curl custom get metho...
- php curl uses post to sen...
- Making a thief program th...
- Chapter16Learn commonly used English words in PHP
- List of commonly used Eng...