Introducing PHP + MySQL to realize paging display of data

1. Connect to the database

$connect = mysqli_connect('localhost', '用户名', '密码', '数据库名') or die('数据库连接失败');mysqli_set_charset($connect, 'utf8');

Related free learning recommendations: mysql video tutorial

2. Constructing SQL statements

Paging is actually implemented using the limit keyword in MySQL. For example, we query the first row of a table. Two pieces of data:

select * from student limit 0, 2

limit requires two parameters. The first parameter represents the number of items to start fetching; the second parameter represents how many items to fetch at a time.
Then the meaning expressed by the above SQL statement is to start from the 0th item and take 2 items (in fact, 0 is the 1st item, and counting starts from 0).

We take the first two pieces of data obtained as the first page.

Then continue to fetch the second page, there are still 2 pieces of data, then the SQL statement is:

select * from student limit 2, 2

The third page:

select * from student limit 4, 2

and so on , we found that there are always two items per page, so the second parameter (i.e. 2) of limit has been determined, so how to determine the first parameter? In fact, the rules are as follows:

Page 1: 0, 2 (Page 1 is taken from the position of 0, and 2 are taken, that is, 0 1)
Page 2: 2, 2 (Page 2 Page is taken from the position of 2, and 2 entries are taken, that is, 2 3)
Page 3: 4, 2 (Page 3 is taken from the position of 4, and 2 entries are taken, that is, 4 5)
Page 4: 6, 2 (Page 4 is taken from the position of 6, and 2 items are taken, that is, 6 7)
...

So the starting position of each page = (current page - 1) * Display on each page Number of entries

Page 1: (1 - 1) * 2 = 0
Page 2: (2 - 1) * 2 = 2
Page 3: (3 - 1) * 2 = 4
……

3. Define the number of items displayed on each page

$pageSize = 2;

4. Define the current page

$page = 1;

5. Calculate according to the formulalimitThe first parameter required

$start = ($page - 1) * $pageSize;

So the SQL statement is:

select * from student limit $start, $pageSize;

At this time, change the value of $page, and you can query the corresponding page data.

6. Pass parameters through the address bar to get the $page parameter to define the current page

In order to be more flexible, we pass parameters through the address bar to get the number of pages we want to display. , so the code in step 4 is changed to:

if ($_GET['page']) {
	$page = $_GET['page'];} else {
	$page = 1; // 接收不到按照 1 处理，即默认第 1 页}

7. Use buttons to control the up and down page switching

Execute the SQL statement:

$sql = "select * from student limit $start, $pageSize";$query = mysqli_query($connect, $sql);$result = mysqli_fetch_all($query, MYSQLI_ASSOC);

The results are output to the HTML table:

<table border="1" cellspacing="0" cellpadding="0" style="width: 50%;text-align: center;">
	<caption>学生列表</caption>
	<tr>
		<td>ID</td>
		<td>姓名</td>
		<td>年龄</td>
		<td>性别</td>
		<td>手机号</td>
	</tr>
	<?php foreach ($result as $v) { ?>
	<tr>
		<td><?php echo $v[&#39;id&#39;]; ?></td>
		<td><?php echo $v[&#39;name&#39;]; ?></td>
		<td><?php echo $v[&#39;age&#39;]; ?></td>
		<td><?php echo $v[&#39;sex&#39;]; ?></td>
		<td><?php echo $v[&#39;mobile&#39;]; ?></td>
	</tr>
	<?php } ?></table>

Add up and down page buttons:

<button>上一页</button><button>下一页</button>

When clicking previous page, we need to give the current page $page - 1;
When clicking Next Page, we need to give the current page $page 1;

based on the current page Determine the previous page:

$up = $page - 1;

Assume that the current page is page 2, then $up is 2 - 1 = 1, that is, the previous page is page 1.

Determine the next page based on the current page:

$next = $page + 1;

Assume that the current page is page 2, then $next is 2 1 = 3, that is, the following One page is page 3.

So we add a hyperlink to the button, link to the current page, and carry a page parameter, the value is the corresponding page number.

<a href="form-7.php?page=<?php echo $up; ?>"><button>上一页</button></a><a href="form-7.php?page=<?php echo $next; ?>"><button>下一页</button></a>

But $up and $next are not infinitely adding or subtracting. When it is already the first page, $up cannot Continue to reduce, so you need to add a judgment when determining the previous page.

if ($page == 1) {
	$up = 1;} else {
	$up = $page - 1;}

Similarly, when determining the next page, you also need to judge. When can you not continue to the next page? When the current page is already the last page, you cannot continue to the next page. How to determine the last page depends on the total number of entries in the current table. If the current table has 10 pieces of data and each page displays 2 pieces, then it needs to be divided into 10/ 2 = 5 pages, this 5 is the last page. If there are 11 pieces of data, then it needs to be divided into 11 / 2 = 5.5 pages, which means that there is 1 piece left after dividing into 5 pages, so it is divided into 6 pages directly, that is, rounded up, then This 6 is the last page.

Find the total number of items:

$sql = "select * from student";$query = mysqli_query($connect, $sql);$num = mysqli_num_rows($query);

According to The total number of items and The number of items displayed on each pageFind out how many pages need to be divided:

$totalPage = ceil($num / $pageSize);

So when judging the next page, change it to:

if ($page == $totalPage) {
	$next = $totalPage;} else {
	$next = $page + 1;	}

8. Complete code

form-7.php

<?php// 连接数据库$connect = mysqli_connect(&#39;localhost&#39;, &#39;用户名&#39;, &#39;密码&#39;, &#39;数据库名&#39;) or die(&#39;数据库连接失败&#39;);mysqli_set_charset($connect, &#39;utf8&#39;);// 查询总条数$sql = "select * from student";$query = mysqli_query($connect, $sql);// 返回结果集中行的数量$num = mysqli_num_rows($query);// 定义每页显示几条$pageSize = 2;// 一共分几页$totalPage = ceil($num / $pageSize);// 获取当前页if ($_GET[&#39;page&#39;]) {
	$page = $_GET[&#39;page&#39;];} else {
	$page = 1;}// 在当前页的基础上确定上一页if ($page == 1) {
	$up = 1;} else {
	$up = $page - 1;}// 在当前页的基础上确定下一页if ($page == $totalPage) {
	$next = $totalPage;} else {
	$next = $page + 1;	}// 求出 limit 的第一个参数$start = ($page - 1) * $pageSize;$sql = "select * from student limit $start, $pageSize";$res = mysqli_query($connect, $sql);$result = mysqli_fetch_all($res, MYSQLI_ASSOC);?><!doctype html><html lang="en">
 <head>
  <meta charset="UTF-8">
  <meta name="Generator" content="EditPlus®">
  <meta name="Author" content="">
  <meta name="Keywords" content="">
  <meta name="Description" content="">
  <title>Document</title>
 </head>
 <body>
	<table border="1" cellspacing="0" cellpadding="0" style="width: 50%;text-align: center;">
		<caption>学生列表</caption>
		<tr>
			<td>ID</td>
			<td>姓名</td>
			<td>年龄</td>
			<td>性别</td>
			<td>手机号</td>
		</tr>
		<?php foreach ($result as $v) { ?>
		<tr>
			<td><?php echo $v[&#39;id&#39;]; ?></td>
			<td><?php echo $v[&#39;name&#39;]; ?></td>
			<td><?php echo $v[&#39;age&#39;]; ?></td>
			<td><?php echo $v[&#39;sex&#39;]; ?></td>
			<td><?php echo $v[&#39;mobile&#39;]; ?></td>
		</tr>
		<?php } ?>
	</table>
	<a href="form-7.php?page=<?php echo $up; ?>"><button>上一页</button></a>
	<a href="form-7.php?page=<?php echo $next; ?>"><button>下一页</button></a>
 </body></html>

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Introducing PHP + MySQL to realize paging display of data. For more information, please follow other related articles on the PHP Chinese website!