You should know php+mysql paging that thing _php skills

The saying goes, "工欲善其事, its prerequisite," we're going to use PHP today to implement pagination. Then our first task is to build a PHP working environment.

Environmental preparedness

Using PHP technology, the best partner is the amp (apache,mysql,php), now there are many integrated environments, such as Wamp,xampp,phpnow and so on. But today I have to manually build a set of PHP working environment.

Apache

We first need to download Apache server to Apache's official web site. It is best to download the MSI version, because we can not manually configure a variety of environments.

Apache Download Address: An MSI version of the Apacheserver, quickly build a PHP server environment first choice.

Mysql

MySQL, a well-known Open-source project in the database world, is now being acquired by Oracle, and does not know that it will not be charged in the future. But for now, the best option for PHP development is MySQL. This needless to say, now attached to the download address.

MySQL Download address

Remember to keep the username and password in mind during the installation process.

Php

Some people say that PHP is not a language, but a framework, a client connection to MySQL implementation. I think carefully, it seems that there is a point of truth. But if so, there are many languages that are not language. PHP, as a civilian hero, is known for its great progress. Download the PHP address below to avoid looking for it separately.

PHP Download Address: MSI version of PHP, no need to manually configure the environment to achieve a fast PHP build

Working environment

Once we have installed the above three software, we can formally start to build the environment. All we need to know right now is that our working directory is in the Apache Htdocs folder, and the htdocs of the virtual directory is maintained by the Apache profile, and we'll come in touch with it later.

Remember that the Htdocs folder is in the Apache installation directory.

Database Preparation

Although the environment is built, but want to do pagination. First of all, data is not. "Ching". Let's prepare the data below.

Build a library

Build DATABASE statement CREATE DATABASE my_database_name;
This is the MySQL database that you use when installing MySQL. It's easier.

Building a table

The Data warehouse is still in place, and now we want to "separate the room", which is the data store, the table.

CREATE TABLE table_name (... ）；
also here for the lazy use of the database table with the. Detailed information is as follows:

mysql> use MySQL
Database changed
mysql> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+---------------------------- -+
| Field   | Type  | Null | Key | Default  | Extra   |
+--------------------------+---------------------+------+-----+-------------------+---------------------------- -+
| database_name |  VARCHAR |  NO | PRI | NULL  | | |    TABLE_NAME |  varchar  | NO | PRI | NULL  | | |
| last_update |  timestamp  | NO | | Current_timestamp | On Update Current_timestamp |
| N_rows   | bigint (unsigned) | NO | | NULL | | | |    clustered_index_size |
bigint unsigned | NO | | NULL | | | |    sum_of_other_index_sizes |
bigint unsigned | NO | | NULL  |    |
+--------------------------+---------------------+------+-----+-------------------+---------------------------- -+

Pre-stored data

For the convenience of demonstration, we need to keep some data in advance. It doesn't matter whether you use a bulk import or a manual addition. The core or

INSERT INTO table_name (...) VALUES (...) ;

For example, we store good data 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 | +-----------------+----------------+---------------------+--------+----------------------+---------------------

 -----+ rows in Set (0.00 sec)

PHP Development Preparation

If the non-MSI version of the installation, we need to manually open the PHP extension, so as to use some MySQL functions to manipulate the database.

Ini

This file is located in the installation directory of PHP. We need to remove the semicolon before the following code.

[Php_mysql]
Extension=php_mysql.dll
[Php_mysqli]
Extension=php_mysqli.dll

In the PHP INI file, the comment is;

Page-Splitting principle

"Everything is only owed to the east Wind," now to say the core idea of pagination.

That is the current page, the page size, the total number of records. With these three total records and page sizes, we can calculate the total number of pages. The corresponding display is then implemented according to the current page.

Total number of records

Gets 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 number of records:". $total _records[0]. " <br> ";

Current page

Get the Client Access page number
$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 access is:". $current _page_number;

Paging Core

Get to the page you want to access and the size of the page, start paging below
$begin _position = ($current _page_number-1) * $page _size;
$sql = "SELECT * from Innodb_table_stats limit $begin _position, $page _size";
$result = mysql_query ($sql);

So we can get the result set we want. The next step is how to show on the page.

Page Show

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> "; echo "<td>". $row [4]. "
 </td> "; echo "<td>". $row [5]. "
 </td> ";
echo "</tr>";

echo "</table>";
Loop display Total pages?> <?php echo ' <a href= ' slicepage.php?page_number=1 ' > Home </a>   '; For ($i =1 $i <= $total _pages $i + +) {echo ' <a href= './slicepage.php?page_number= '. $i. ' > '. $i. ' Page </a> 
   '; Echo ' <a href= ' slicepage.php?page_number= '. ($current _page_number-1). "> Prev </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 </a>   ';

Paging implementation

After you've learned the above, take a look at the complete example below.

Code slicepage.php

<meta charset= "Utf-8" > <?php//Solve Chinese garbled, found not effective, then consider the MySQL client garbled situation header ("Content-type=text/html;charset=utf-8

");
$host = "localhost";
$username = "root";
$password = "MySQL";

$dbname = "MySQL";
Start getting the database connection $conn = mysql_connect ($host, $username, $password) or Die (Mysql_error ());
Manually change the client encoding mysql_query ("Set names UTF8");

Choose which database to use mysql_select_db ($dbname);
Gets 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 number of records:". $total _records[0]. "


<br> ";
Get the total number of pages, generally the small size of the page fixed, so here is set aside as a page of 5 data $page _size = 3;
$total _pages = ceil ($total _records[0]/$page _size);


echo "Total pages:". $total _pages;
Get the Client Access page number $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 access is:". $Current_page_number;
Get to the page you want to access and the size of the page, start paging below $begin _position = ($current _page_number-1) * $page _size;
$sql = "SELECT * from Innodb_table_stats limit $begin _position, $page _size";


$result = mysql_query ($sql);
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> "; echo "<td>". $row [4]. "
 </td> "; echo "<td>". $row [5]. "
 </td> ";
echo "</tr>";

echo "</table>"; Loop display Total pages?> <?php echo ' <a href= ' slicepage.php?page_number=1 ' > home page </a> &nBSP; '; For ($i =1 $i <= $total _pages $i + +) {echo ' <a href= './slicepage.php?page_number= '. $i. ' > '. $i. ' Page </a> 
   '; Echo ' <a href= ' slicepage.php?page_number= '. ($current _page_number-1). "
> Prev </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 </a>   ';
Release Data connection resource mysql_free_result ($result);

Mysql_close ($conn);

 ?>

The resulting initial page is:

Click on page number

Next page

Summarize

Paging is a very practical technology, compared to the implementation of Java, PHP to achieve flexibility is still very high. Free of the tedious object-oriented programming, in the clarity of the time can indeed give people a sense of beauty.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.