mysql查询结果分页_分页MySQL查询结果

╰+哭是因爲堅強的太久メ 2022-12-07 12:09 331阅读 0赞

mysql查询结果分页

As your database grows, showing all the results of a query on a single page is no longer practical. This is where pagination in [PHP][] and [MySQL][] comes in handy. You can display the results over a number of pages, each linked to the next, to allow your users to browse the content [on your website][] in bite-sized pieces.

随着数据库的增长，在单个页面上显示查询的所有结果不再可行。 这是在[PHP][]和[MySQL][]中进行分页的地方。 您可以在多个页面上显示结果，每个页面都链接到下一页，以使您的用户可以按大小浏览[网站上][on your website]的内容。

## 设置变量 **(** Setting the Variables **)** ##

The code below first connects to the database. Then you need to know which page of results to display. The** if (!(isset($pagenum)))** code checks if the page number** ($pagenum)** isn't set, and if so, sets it to 1. If there is a page number already set, this code is ignored.

下面的代码首先连接到数据库。 然后，您需要知道要显示结果的哪一页。 **if(！(isset($ pagenum)))**代码检查是否未设置页码**($ pagenum)** ，如果已设置，则将其设置为1。如果已经设置了页码，则将忽略此代码。

You run the query. The **$data** line should be edited to apply to your site and to return what you need to count results. The **$rows** line then simply counts the number of results for your query.

您运行查询。 **$ data**行应进行编辑以应用于您的网站并返回计算结果所需的内容。 **$ rows**行然后简单地计算查询的结果数。

Next, you define **$page\_rows**, which is the number of results you want to display on each page before moving to the next page of results. You can then calculate the total number of pages you have **($last)** by dividing the total amount of results (rows) by the number of results you want per page. Use CEIL here to round all numbers up to the next whole number.

接下来，定义**$ page\_rows** ，这是在**移至**下一页结果之前要在每页上显示的结果数。 然后，您可以通过将结果总数(行)除以每页所需的结果数，来计算总页数**($ last)** 。 在此处使用CEIL将所有数字四舍五入到下一个整数。

Next, the code runs a check to make sure the page number is valid. If the number is less than one or greater than the total number of pages, it resets to the closest page number with content.

接下来，代码运行检查以确保页码有效。 如果该页数少于一或大于总页数，它将重置为内容最接近的页码。

Finally, you set the range **($max )** for the results using the [LIMIT function][]. The starting number is determined by multiplying the results per page by one less than the current page. The duration is the number of results that display per page.

最后，您使用[LIMIT函数][LIMIT function]设置结果的范围**($ max)** 。 起始编号是通过将每页的结果乘以当前页的少一来确定的。 持续时间是每页显示的结果数。

## 设置分页变量的代码 **(** Code for Setting Pagination Variables **)** ##

<?php

<？php

// Connects to your Database

//连接到数据库

mysql\_connect("your.hostaddress.com", "username", "password") or die(mysql\_error());

mysql\_connect(“ your.hostaddress.com”，“ username”，“ password”)或die(mysql\_error());

mysql\_select\_db("address") or die(mysql\_error());

mysql\_select\_db(“ address”)或die(mysql\_error());

//This checks to see if there is a page number. If not, it will set it to page 1

//这会检查是否有页码。 如果不是，它将设置为第1页

if (!(isset($pagenum)))

如果(！(isset($ pagenum)))

$pagenum = 1;

$ pagenum = 1;

//Here we count the number of results

//这里我们计算结果数

//Edit $data to be your query

//编辑$ data作为查询

$data = mysql\_query("SELECT \* FROM topsites") or die(mysql\_error());

$ data = mysql\_query(“ SELECT \* FROM topsites”)或die(mysql\_error());

$rows = mysql\_num\_rows($data);

$ rows = mysql\_num\_rows($ data);

//This is the number of results displayed per page

//这是每页显示的结果数

$page\_rows = 4;

$ page\_rows = 4;

//This tells us the page number of our last page

//这告诉我们最后一页的页码

$last = ceil($rows/$page\_rows);

$ last = ceil($ rows / $ page\_rows);

//this makes sure the page number isn't below one, or more than our maximum pages

//这可确保页码不小于一个或大于我们的最大页面数

if ($pagenum < 1)

如果($ pagenum <1)

$pagenum = 1;

$ pagenum = 1;

elseif ($pagenum > $last)

elseif($ pagenum> $ last)

$pagenum = $last;

$ pagenum = $ last;

//This sets the range to display in our query

//这将设置要显示在查询中的范围

$max = 'limit ' .($pagenum - 1) \* $page\_rows .',' .$page\_rows;

$ max ='limit'。($ pagenum-1)\* $ page\_rows。'，'。$ page\_rows;

## 查询和结果 **(** Query and Results **)** ##

This code reruns the query from earlier, only with one slight change. This time it includes the **$max** variable to limit the query results to those that belong on the current page. After the query, you display the results as normal using any formatting you wish.

此代码仅稍作更改即可从较早的时间重新运行查询。 这次它包含**$ max**变量，以将查询结果限制为属于当前页面的结果。 查询后，您可以使用所需的任何格式正常显示结果。

When the results are displayed, the current page is shown along with the total number of pages that exist. This is not necessary, but it is nice information to know.

显示结果时，将显示当前页面以及现有的页面总数。 这不是必需的，但它是很好的信息。

Next, the code generates the navigation. The assumption is that if you are on the first page, you don't need a link to the first page. As it is the first result, no previous page exists. So the code checks** (if ($pagenum == 1) )** to see if the visitor is on page one. If so, then nothing happens. If not, then PHP\_SELF and the page numbers generate links to both the first pageand the previous page.

接下来，代码生成导航。 假设是，如果您位于第一页，则不需要指向第一页的链接。 由于它是第一个结果，因此不存在上一页。 因此，代码检查**(if($ pagenum == 1))**来查看访问者是否在第一页上。 如果是这样，则什么也不会发生。 如果不是，则PHP\_SELF和页码会生成指向第一页和上一页的链接。

You do almost the same thing to generate the links on the other side. However, this time you are checking to make sure you aren't on the last page. If you are, then you don't need a link to the last page, nor does a next page exist.

您执行几乎相同的操作来生成另一侧的链接。 但是，这一次您正在检查以确保您不在最后一页。 如果是这样，则不需要链接到最后一页，也不需要下一页。

## 分页结果代码 **(** Code for Pagination Results **)** ##

//This is your query again, the same one... the only difference is we add $max into it

//这又是您的查询，是同一查询...唯一的区别是我们在其中添加了$ max

$data\_p = mysql\_query("SELECT \* FROM topsites $max") or die(mysql\_error());

$ data\_p = mysql\_query(“ SELECT \* FROM topsites $ max”)或die(mysql\_error());

//This is where you display your query results

//在这里显示查询结果

while($info = mysql\_fetch\_array( $data\_p ))

while($ info = mysql\_fetch\_array($ data\_p))

Print $info\['Name'\];

打印$ info \['Name'\];

echo " ";

回声“ ”;

echo "";

回声“ ”;

// This shows the user what page they are on, and the total number of pages

//这会向用户显示他们所在的页面以及页面总数

echo " --Page $pagenum of $last-- ";

回声“-$ pagelast-$ p --page $ pagenum” “;

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.

//首先，我们检查是否在第一页上。 如果是这样，我们就不需要指向上一页或首页的链接，因此我们什么也不做。 如果不是，则生成指向第一页和上一页的链接。

if ($pagenum == 1)

如果($ pagenum == 1)

else

其他

echo " <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=1'> <<-First</a> ";

echo“ <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=1'> <<-第一</ a>”;

echo " ";

回声“”;

$previous = $pagenum-1;

$ previous = $ pagenum-1;

echo " <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$previous'> <-Previous</a> ";

echo“ <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$previous'> <-上一个</ a>”;

//just a spacer

//只是一个间隔

echo " ---- ";

回声“ ----”;

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

//这和上面的一样，只检查我们是否在最后一页上，然后生成Next和Last链接

if ($pagenum == $last)

如果($ pagenum == $ last)

else \{

其他\{

$next = $pagenum+1;

$ next = $ pagenum + 1;

echo " <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$next'>Next -></a> ";

echo“ <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$next'> Next-> </a>”;

echo " ";

回声“”;

echo " <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$last'>Last ->></a> ";

echo“ <a href='\{$\_SERVER\['PHP\_SELF'\]\}?pagenum=$last'> Last->> </a>”;

？>

> 翻译自: [https://www.thoughtco.com/pagination-of-mysql-query-results-2694115][https_www.thoughtco.com_pagination-of-mysql-query-results-2694115]

mysql查询结果分页

[PHP]: https://www.thoughtco.com/beginners-guide-to-php-programing-2693961
[MySQL]: https://www.thoughtco.com/learn-sql-mysql-2693872
[on your website]: https://www.thoughtco.com/why-use-php-2694006
[LIMIT function]: https://www.thoughtco.com/limit-mysql-command-2693970
[https_www.thoughtco.com_pagination-of-mysql-query-results-2694115]: https://www.thoughtco.com/pagination-of-mysql-query-results-2694115