What You Should Know About PHP + MySQL paging: phpmysql Paging

What You Should Know About PHP + MySQL paging: phpmysql Paging

As the saying goes, "to do good things, you must first sharpen the tool." Today we will use PHP to implement paging. So our first task is to set up the working environment of PHP.

Environment preparation

Using PHP technology, the best partner is AMP (Apache, MySQL, PHP). Now there are many integrated environments, such as WAMP, XAMPP, and phpnow. But today I am going to build a PHP working environment manually.

Apache

First, we need to download Apache server from the Apache official website. It is best to download the msi version, because we do not need to manually configure various environments.

Apache: An msi version of ApacheServer, which is the first choice to quickly build a PHP server environment.

MySQL

MySQL, a well-known open-source project in the database field, is now also acquired by Oracle. I don't know if it will charge fees in the future. But for now, the best choice for PHP development is MySQL. This is not required.

MySQL

Remember the user name and password during installation.

PHP

Some people say that PHP is not a language, but a framework and a client to connect to MySQL. I thought about it carefully. It seems that there are some reasons. However, in this case, many languages are not considered language. As a civilian hero, php is well known for its progress. The following is an example of php, so that you do not have to look for it separately.

PHP: msi version of PHP, which can be quickly built without manual environment Configuration

Work Environment

After we have installed the above three software, we can start to build the environment. We only need to know that our working directory is under the Apache htdocs folder. The htdocs as the virtual directory is maintained by the apache configuration file, we will be able to see it later.

Remember to use the htdocs folder in the apache installation directory.

Database preparation

Although the environment has been set up, you need to perform paging. The data is not required first ." It's hard for us to have no rice. Now let's prepare the data.

Database creation

Create database my_database_name;
Here we will use the MySQL database that comes with mysql during installation. It also saves time.

Create a table

The data warehouse is still built. Now we want to "Separate Rooms", that is, the data storage location and table.

Create table table_name (···);
We also use our own database tables for laziness. The details are as follows:

mysql> use mysqlDatabase changedmysql> 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

To facilitate the demonstration, we need to pre-store some data. It doesn't matter whether batch import or manual addition is used. Core or

Insert into table_name (...) values (···);

For example, the stored data 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 not an msi version, we need to manually enable PHP extension so that some functions of mysql can be used to operate the database.

Php. ini

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

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

In the php ini file, the comment is;

Paging Principle

"Everything is ready for nothing", now let's talk about the core idea of paging.

That is, the current page, page size, and total number of records. The total number of records and page size can be used to calculate the total number of pages. Then, the corresponding display is implemented based on the current page.

Total number of records

// Obtain the total number of records $ records = "select count (*) from innodb_table_stats"; $ records = mysql_query ($ records); $ total_records = mysql_fetch_row ($ total_records_result ); echo "Total record digits :". $ total_records [0]. "<br> ";

Current page

// Obtain the page number accessed by the client through 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 "the page number to be accessed is :". $ current_page_number;

Paging Core

// Get the page to access and the page size, the following start page $ 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 desired result set. The next step is how to display on the page.

Page display

// Processing result set echo "<table border = '# CCF solid 1px'> <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>"; ech O "<td> ". $ row [4]. "</td>"; echo "<td> ". $ row [5]. "</td>"; echo "</tr>" ;}echo "</table>"; // the total number of pages displayed cyclically?> <? Phpecho '<a href = "SlicePage. php? Page_number = 1 "> homepage </a> '; for ($ I = 1; $ I <= $ total_pages; $ I ++) {echo' <a href = ". /SlicePage. php? Page_number = '. $ I.' "> page '. $ I.' </a> ';} echo' <a href =" SlicePage. php? Page_number = '. ($ current_page_number-1).' "> previous page </a> '; echo' <a href =" SlicePage. php? Page_number = '. ($ current_page_number + 1).' "> next page </a> '; echo' <a href =" SlicePage. php? Page_number = '. ($ total_pages).' "> last page </a> ';

Paging implementation

After learning about the above content, let's take a look at this complete example.

Code: SlicePage. php

<Meta charset = "UTF-8"> <? Php // solves Chinese garbled characters and finds that it cannot work, consider the MySQL client garbled header ("Content-type = text/html; charset = UTF-8 "); $ host = "localhost"; $ username = "root"; $ password = "mysql"; $ dbname = "mysql "; // start to obtain the database connection $ conn = mysql_connect ($ host, $ username, $ password) or die (mysql_error ()); // manually change the client encoding mysql_query ("set names utf8"); // select the database mysql_select_db ($ dbname) to be used ); // obtain the 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 record digits :". $ total_records [0]. "<br>"; // obtain the total number of pages. Generally, the page size is fixed. Therefore, it is set to 5 pieces of data on one page. $ page_size = 3; $ total_pages = ceil ($ total_records [0]/$ page_size); echo "total page number :". $ total_pages; // obtain the page number accessed by the client through 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 "the page number to be accessed is :". $ current_page_number; // get the page to be accessed and the page size. Start pagination below $ begin_position = ($ current_page_number-1) * $ page_size; $ SQL = "select * from innodb_table_stats limit $ begin_position, $ page_size"; $ result = mysql_query ($ SQL); // process result set echo "<table border = '# CCF solid 1px'> <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>"; e Cho "<td> ". $ row [5]. "</td>"; echo "</tr>" ;}echo "</table>"; // the total number of pages displayed cyclically?> <? Phpecho '<a href = "SlicePage. php? Page_number = 1 "> homepage </a> '; for ($ I = 1; $ I <= $ total_pages; $ I ++) {echo' <a href = ". /SlicePage. php? Page_number = '. $ I.' "> page '. $ I.' </a> ';} echo' <a href =" SlicePage. php? Page_number = '. ($ current_page_number-1).' "> previous page </a> '; echo' <a href =" SlicePage. php? Page_number = '. ($ current_page_number + 1).' "> next page </a> '; echo' <a href =" SlicePage. php? Page_number = '. ($ total_pages).' "> last page </a> '; // release the data connection resource mysql_free_result ($ result); mysql_close ($ conn);?>

Result initial Page:

Page number

Next Page

Summary

Paging is a very practical technology. Compared with Java Implementation, PHP is flexible to implement. This frees us from the tedious object-oriented programming, which can provide us with a sense of beauty when we have a clear idea.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.