Things you should know about PHP+MySQL paging

What you should know about PHP MySQL paging. This article mainly introduces PHP MySQL paging technology in detail and provides you with complete PHP paging examples. Interested friends can refer to it

As the saying goes, "If you want to do your job well, you must first sharpen your tools." Today we are going to use PHP to implement paging. Then our first task is to build a PHP working environment.

Environment preparation

When using PHP technology, the best partner is AMP (Apache, MySQL, PHP). Now there are many integrated environments, such as WAMP, XAMPP , phpnow and so on. But today I will manually build a PHP working environment.

Apache

We first need to download the Apache server from Apache’s official website. It is best to download the msi version, because then we can configure various environments without having to manually configure it.

Apache download address: an msi version of ApacheServer, the first choice for quickly building a PHP server environment.

MySQL

MySQL, a well-known open source project in the database industry, has now been acquired by Oracle. I don’t know if there will be any charges in the future. But for now, the best choice for PHP development is MySQL. Needless to say, here is the download address.

MySQL download address

During the installation process, remember to remember the user name and password.

PHP

#Some people say that PHP is not a language, but a framework, a client implementation that connects to MySQL. I thought about it carefully, and it seemed to make some sense. But if you put it this way, there are many languages ​​that are not languages ​​at all. As a civilian hero, php's progress is well known. Attached is the download address of php below, so you don’t have to look for it separately.

PHP download address: msi version of PHP, you can quickly build PHP without manually configuring the environment

Working environment

We have installed it After the above three software, you can officially start setting up the environment. For now, all we need to know is that our working directory is under Apache's htdocs folder. htdocs, as a virtual directory, is maintained by the apache configuration file, which we will gradually come into contact with in the future.

Remember that it is the htdocs folder in the apache installation directory.

Database preparation

Although the environment has been set up, we still need to do paging. First of all, there must be data. "Make bricks without straw". Next let's prepare the data.

Create database

Create database statement create database my_database_name;
Here we use MySQL that comes with installation The mysql database is ready. It’s also a little easier.

Building tables

The data warehouse is still built, now we need to "separate rooms", which is where the data is stored ,surface.

create table table_name（···）；
Similarly, here we use the built-in database table for laziness. The details are as follows:

mysql> use mysql
Database changed
mysql> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field   | Type  | Null | Key | Default  | Extra   |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name  | varchar(64)  | NO | PRI | NULL  |    |
| table_name  | varchar(64)  | NO | PRI | NULL  |    |
| last_update  | timestamp  | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows   | bigint(20) unsigned | NO | | NULL  |    |
| clustered_index_size | bigint(20) unsigned | NO | | NULL  |    |
| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL  |    |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+

Pre-stored data

For the convenience of demonstration, we need to pre-store some data. It doesn't matter whether you use batch import or add manually. The core is still

insert into table_name(···) values(···);

For example, the data we store is as follows:

mysql> select * from innodb_table_stats;
+-----------------+----------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update  | n_rows | clustered_index_size | sum_of_other_index_sizes |
+-----------------+----------------+---------------------+--------+----------------------+--------------------------+
| fams  | admin  | 2016-07-19 14:47:02 | 3 |   1 |   0 |
| fams  | assets_in | 2016-07-14 14:42:44 | 2 |   1 |   3 |
| fams  | assets_out | 2016-07-14 20:14:31 | 4 |   1 |   3 |
| fams  | class  | 2016-07-14 14:36:02 | 3 |   1 |   0 |
| fams  | dog  | 2016-08-11 15:25:50 | 4 |   1 |   0 |
| fams  | fixed_assets | 2016-07-14 15:55:09 | 6 |   1 |   2 |
| fams  | sub_class | 2016-07-14 14:38:51 | 8 |   1 |   1 |
| fams  | user  | 2016-07-14 14:15:59 | 2 |   1 |   0 |
| mysql  | gtid_executed | 2016-07-14 12:50:25 | 0 |   1 |   0 |
| privilegesystem | privilege | 2016-08-08 08:56:21 | 3 |   1 |   0 |
| privilegesystem | role  | 2016-08-08 08:26:56 | 2 |   1 |   0 |
| privilegesystem | role_privilege | 2016-08-08 09:51:04 | 2 |   1 |   1 |
| privilegesystem | user  | 2016-08-08 11:07:35 | 2 |   1 |   0 |
| privilegesystem | user_role | 2016-08-08 11:08:15 | 2 |   1 |   2 |
| sys  | sys_config | 2016-07-14 12:50:30 | 6 |   1 |   0 |
| test  | datetest | 2016-07-19 10:02:38 | 2 |   1 |   0 |
+-----------------+----------------+---------------------+--------+----------------------+--------------------------+
16 rows in set (0.00 sec)

PHP expansion preparation

If it is a non-msi version installation, we need to manually enable PHP expansion so that Use some functions of mysql to operate the database.

php.ini

This file is located in the php installation directory. We need to remove the semicolon in front of the code below.

[PHP_MYSQL]
extension=php_mysql.dll
[PHP_MYSQLI]
extension=php_mysqli.dll

In the PHP ini file, The comment is;

Principle of paging

"Everything is ready, all we need is the east wind." Now let’s talk about the core idea of ​​paging.

That is the current page, page size, and total number of records. Through these three, we can calculate the total number of pages through the total number of records and page size. Then implement the corresponding display based on the current page.

Total number of records

// 获取总的记录数
$sql_total_records = "select count(*) from innodb_table_stats";
$total_records_result = mysql_query($sql_total_records);
$total_records = mysql_fetch_row($total_records_result);
echo "总的记录数位： ".$total_records[0]."<br>";

Current page

// 通过GET方式获得客户端访问的页码
$current_page_number = isset($_GET['page_number'])?$_GET['page_number']:1;
if($current_page_number<1) {
 $current_page_number =1;
}
if($current_page_number>$total_pages){
 $current_page_number = $total_pages;
}
echo "要访问的页码为：".$current_page_number;

Paging Core

// 获取到了要访问的页面以及页面大小，下面开始分页
$begin_position = ($current_page_number-1)*$page_size;
$sql = "select * from innodb_table_stats limit $begin_position,$page_size";
$result = mysql_query($sql);

In this way we can get the result set we want. The next thing is how to display it on the page.

Page display

// 处理结果集
echo "<table border=&#39;#CCF solid 1px&#39;><th>Mysql Fixed Assets Table</th>";
echo "<tr><td>DbName</td><td>TableName</td><td>Last_update</td><td>n_Nows</td><td>Clustered_Index_Size</td><td>Sum_od_Other_Index_sizes</td></tr>";
while(($row = mysql_fetch_row($result))){
 echo "<tr>";
 echo "<td>".$row[0]."</td>";
 echo "<td>".$row[1]."</td>";
 echo "<td>".$row[2]."</td>";
 echo "<td>".$row[3]."</td>";
 echo "<td>".$row[4]."</td>";
 echo "<td>".$row[5]."</td>";
 echo "</tr>";
}
echo "</table>";

// 循环显示总页数
?>
<?php
echo &#39;<a href="SlicePage.php?page_number=1">首页</a>  &#39;;
for($i=1;$i<=$total_pages;$i++){
 echo &#39;<a href="./SlicePage.php?page_number=&#39;.$i.&#39;">第&#39;.$i.&#39;页</a>  &#39;; 
}
echo &#39;<a href="SlicePage.php?page_number=&#39;.($current_page_number-1).&#39;">上一页</a>  &#39;;
echo &#39;<a href="SlicePage.php?page_number=&#39;.($current_page_number+1).&#39;">下一页</a>  &#39;;
echo &#39;<a href="SlicePage.php?page_number=&#39;.($total_pages).&#39;">尾页</a>  &#39;;

Paging implementation

After understanding the above content, let’s take a look at this complete example. Bar.

Code SlicePage.php

The resulting initial page is:

Click on the page number

Next page

Summary

Pagination is a very Practical technology, compared to Java implementation, PHP is still very flexible to implement. It frees people from cumbersome object-oriented programming and can indeed give people a sense of beauty when their ideas are clear.

The above is the detailed content of Things you should know about PHP+MySQL paging. For more information, please follow other related articles on the PHP Chinese website!