使用jQuery，AJAX和PHP进行分页

tech2022-09-06 114

In this article, we’re going to explain how easy it is to paginate your data set using PHP and AJAX via jQuery. We’re also going to use the Silex framework for simplicity.

在本文中，我们将解释使用PHP和AJAX通过jQuery对数据集进行分页有多么容易。为了简化起见，我们还将使用Silex框架。

数据来源 (The data source)

First off, we need some data to paginate!

首先，我们需要一些数据进行分页！

idnameage1Jamie432Joe243Fred234Clive925Roy736Geoff247Ray128John99Pete3210Ralph34 ID 名称年龄 1个杰米 43 2 乔 24 3 弗雷德 23 4 克莱夫 92 5 罗伊 73 6 杰夫 24 7 射线 12 8 约翰 9 9 皮特 32 10 拉尔夫 34

For the purpose of this example we’re going to use MySQL, but of course we can swap this out easily for SQLite or any other RDBMS. We’re going to name our database example and the table will be named people.

就本示例而言，我们将使用MySQL ，但是我们当然可以轻松地将其替换为SQLite或任何其他RDBMS。我们将命名数据库example ，该表将命名为people 。

后端 (The backend)

As we’re going to build our application on Silex, we need to install Silex using Composer first. Install it through composer with the command composer require silex/silex.

当我们要在Silex上构建应用程序时，我们需要先使用Composer安装Silex。使用命令composer require silex/silex通过composer安装它。

Next we need set up our index.php file, connect to the data source and select the database. We’re going to use PDO for this as it is easy to change to other database software later, and it also handles escaping user input (thus preventing SQL injection attacks). If you’re still stuck on the mysqli or even worse the mysql extension, see this tutorial. We’re going to put the connection in the $app container so it’s easy to use later in our routes.

接下来，我们需要设置我们的index.php文件，连接到数据源并选择数据库。我们将为此使用PDO ，因为以后可以轻松更改为其他数据库软件，并且它还可以处理转义的用户输入(从而防止SQL注入攻击)。如果您仍然停留在mysqli甚至更糟糕的mysql扩展上，请参阅本教程。我们将把连接放在$app容器中，以便稍后在我们的路线中使用。

$app['db'] = function () { $host = 'localhost'; $db_name = 'example'; $user = 'root'; $pass = ''; return new \PDO( "mysql:host={$host};dbname={$db_name}", $user, $pass, array(\PDO::ATTR_EMULATE_PREPARES => false) ); };

Now that we’re connected to the database, we’re going to provide three routes in our app, which will enable;

现在我们已经连接到数据库，我们将在我们的应用程序中提供三个路由，这将启用：

Retrieving the section of the result set we want to display

检索我们要显示的结果集部分 Retrieving the total amount of rows in the result set

检索结果集中的总行数 Viewing the HTML frontend

查看HTML前端

The first route is as follows:

第一条路线如下：

$app->get('/data/page/{page_num}/{rows_per_page}', function ($page_num, $rows_per_page) use ($app) { $start = ((int)$page_num - 1) * (int)$rows_per_page; $total_rows = (int)$rows_per_page; $stmt = $app['db']->prepare( 'SELECT `name` FROM `people` ORDER BY `name` LIMIT :from, :total_rows' ); $stmt->bindParam('from', $start); $stmt->bindParam('total_rows', $total_rows); $stmt->execute(); $result = $stmt->fetchAll(\PDO::FETCH_ASSOC); return $app->json($result); });

This enables our frontend to grab a subset of the result set from the database. Two parameters can be provided through the URI; one for the page number and one for how many rows should be on each page. The page number is used alongside the rows per page to work out which row in the result set we need to start retrieving data from.

这使我们的前端可以从数据库中获取结果集的子集。可以通过URI提供两个参数。一个代表页码，另一个代表每页上应有多少行。页码与每页行一起使用，以计算出我们需要从中开始检索数据的结果集中的哪一行。

In this example we are going to paginate all data from the table. However in a real application we will likely need to include a WHERE clause to filter the data returned. For example, if we wanted to just display people who are younger than 30, we would amend the above code to include a WHERE clause in the query:

在此示例中，我们将对表中的所有数据进行分页。但是，在实际的应用程序中，我们可能需要包含WHERE子句以过滤返回的数据。例如，如果我们只想显示30岁以下的人，则可以修改上面的代码以在查询中包含WHERE子句：

$stmt = $app['db']->prepare( 'SELECT `name` FROM `people` WHERE `age` < 30 ORDER BY `name` LIMIT :from, :total_rows' );

The query is using a prepared statement to insert the variables for which page number has been requested and how many rows to output per page. These are provided in the URI and then dynamically inserted into a LIMIT clause in the SQL query.

该查询使用一条准备好的语句来插入已请求页码的变量，以及每页要输出多少行的变量。这些在URI中提供，然后动态插入到SQL查询的LIMIT子句中。

The second route provides the ability to execute a query to return the total amount of rows in the table. This is important because we want to utilize page number links on the frontend. That route should look like this:

第二种途径提供了执行查询以返回表中总行数的能力。这很重要，因为我们要利用前端的页码链接。该路线应如下所示：

$app->get('/data/countrows', function () use ($app) { $stmt = $app['db']->query( 'SELECT COUNT(`id`) AS `total_rows` FROM `people`' ); $result = $stmt->fetch(\PDO::FETCH_ASSOC); return $app->json($result); });

Here we are making use of an aggregate SQL function called COUNT(). This is a GROUP BY function – meaning that it will group the selected rows together to provide a single row. In this case it provides a sum total of all selected rows as an integer.

在这里，我们利用称为COUNT()的聚合SQL函数。这是GROUP BY函数-意味着它将选定的行组合在一起以提供单个行。在这种情况下，它以整数形式提供所有选定行的总和。

Another important backend feature to note is that the data fetching routes should return as JSON, as this will ease the integration into the frontend. Silex takes care of this for us using the JSON helper method.

要注意的另一个重要的后端功能是，数据获取路由应以JSON返回，因为这将简化与前端的集成。 Silex使用JSON helper方法为我们解决了这一问题。

The last route is simply instructing the root URI to output an HTML page.

最后一条路线只是指示根URI输出HTML页面。

$app->get('/', function () use ($app) { return file_get_contents(__DIR__.'/../resources/views/index.html'); });

Which brings us to…

带我们去…

前端 (The frontend)

Now the fun bit!

现在好玩！

We need to make sure our frontend includes jQuery and has a container for both the page number links and the data itself.

我们需要确保我们的前端包括jQuery，并且具有用于页码链接和数据本身的容器。

In this example we’re going to use <ul>s, however in a real application we may want to use a <table> for the data (especially important if we want to display more than one piece of information per row).

在此示例中，我们将使用<ul> ，但是在实际的应用程序中，我们可能希望对数据使用<table> (如果要在每行中显示多个信息，则尤其重要)。

We’ll need two custom functions in Javascript; one to fetch a specific page, and one to initialize the page number links. In our example we’ve also done a little bit of set up before we get to the main section of code:

我们将需要Javascript中的两个自定义函数；一个用于获取特定页面，另一个用于初始化页面编号链接。在我们的示例中，在进入代码的主要部分之前，我们还做了一些设置：

var rows_per_page = 3; var total_rows;

This initializes two global variables for the number of rows we want to show per page and the total amount of rows in the table (the latter will be fetched via AJAX shortly).

这将初始化两个全局变量，分别用于我们希望每页显示的行数和表中的行总数(稍后将通过AJAX提取后者)。

To initialize the page number links we’ll need to make an AJAX call to our PHP script to fetch the total amount of rows in the table. Then we’ll use the success function callback to inject the HTML to link to each page into our page number links container depending on the total amount of rows. It should look something like this:

要初始化页码链接，我们需要对PHP脚本进行AJAX调用，以获取表中的总行数。然后，我们将使用成功函数回调将要链接到每个页面HTML注入到我们的页码链接容器中，具体取决于行的总数。它看起来应该像这样：

Note that we’re injecting the HTML for the page links with a Javascript function call already attached. This means that clicking those links will trigger our page fetching function without us attaching a click event manually. Also note that the href attribute is set to a hash fragment with the page number – this means that the URI will be modified to reflect which page number we’re viewing.

请注意，我们将使用已附加的Javascript函数调用为页面链接注入HTML。这意味着单击这些链接将触发我们的页面获取功能，而无需我们手动附加click事件。还要注意， href属性设置为具有页码的哈希片段–这意味着将修改URI以反映我们正在查看的页码。

The function for fetching a specific page also uses an AJAX call, but has a few key differences;

提取特定页面的功能也使用AJAX调用，但有一些主要区别。

The first difference to note is that the container is cleared first using jQuery’s html() function. This is because this function will be called multiple times, and needs a blank slate to work from each time. The second difference is that the data itself is looped through in the success function callback. This is done using the very useful jQuery each() function.

首先要注意的是，首先使用jQuery的html()函数清除了容器。这是因为此函数将被多次调用，并且每次都需要一个空白板来工作。第二个区别是数据本身在成功函数回调中循环通过。这是使用非常有用的jQuery each()函数完成的。

Both functions use the get() jQuery AJAX shorthand function for making a HTTP GET request. The jQuery ajax() function could be used instead, which allows for more configuration, but the shorthand is just fine for this example.

这两个函数都使用get() jQuery AJAX速记函数来发出HTTP GET请求。可以改用jQuery ajax()函数，它允许进行更多配置，但是对于此示例而言，速记就可以了。

The getPage() function also differs from the initPageNumbers() function as it passes two parameters along with the HTTP GET request. These are provided in an object as the second parameter of the get() function call. These are then processed by the PHP script as defined previously.

getPage()函数也不同于initPageNumbers()函数，因为它传递了两个参数以及HTTP GET请求。这些在对象中作为get()函数调用的第二个参数提供。然后，这些将由前面定义PHP脚本处理。

Now all that’s left is to set up the page initialization logic. To do this we’ll be using the jQuery ready() function;

现在剩下的就是设置页面初始化逻辑了。为此，我们将使用jQuery ready()函数；

$(document).ready(function(){ //Set up the page number links initPageNumbers(); //Set the default page number var page_num = 1; //If there's a hash fragment specifying a page number if(window.location.hash !== '') { //Get the hash fragment as an integer var hash_num = parseInt(window.location.hash.substring(1)); //If the hash fragment integer is valid if(hash_num > 0) { //Overwrite the default page number with the user supplied number page_num = hash_num; } } //Load the first page getPage(page_num); });

This calls our page number initialisation function and fetches a page of the result set to display.

这将调用我们的页码初始化函数，并获取要显示的结果集的页面。

If a hash fragment has been provided on the end of the URI then this will be parsed into a page number and set as the page to show, otherwise page 1 is shown by default. This hash fragment functionality lets search engines index our individual pages rather than just the first page. It also allows us to provide external links directly to a specific page number.

如果在URI的末尾提供了哈希片段，则将其解析为页码并将其设置为要显示的页面，否则默认显示页面1。这种哈希片段功能使搜索引擎可以索引我们的各个页面，而不仅是首页。它还允许我们直接提供指向特定页码的外部链接。

An improvement to this technique would be to provide links such as app.com/page/2, app.com/page/3, etc. The benefits to doing this over using hash fragments or query strings (? suffix on the URI) is that it is better supported by search engines and website crawlers.

对该技术的一种改进是提供诸如app.com/page/2链接。与使用散列片段或查询字符串(URI上的?后缀) app.com/page/3 ，这样做的好处是搜索引擎和网站搜寻器更好地支持它。

One thing to note about our page initialisation logic is that the two AJAX operations will happen asynchronously, meaning that the browser will go off and fetch a page of the data at the same time as it’s figuring out how many page links to display. This is great for user experience as it means less time to wait before getting a full experience.

关于页面初始化逻辑要注意的一件事是，这两个AJAX操作将异步发生，这意味着浏览器将在确定要显示多少页面链接的同时关闭并获取数据页面。这对用户体验非常有用，因为这意味着在获得完整体验之前需要等待的时间更少。

闭幕词和替代解决方案 (Closing remarks and alternative solutions)

The technique of paginating a result set using AJAX is best suited to large result sets, where the amount of data is so large that it is either:

使用AJAX对结果集进行分页的技术最适合大型结果集，在大型结果集中，数据量如此之大，以至于：

Detrimental to browser performance to include and manipulate all of the result set in the DOM at once, or;

不利于浏览器的性能，无法立即包含和处理DOM中的所有结果集；或者 Very slow to fetch and receive the entire result set from the RDBMS in one query.

在一个查询中从RDBMS获取和接收整个结果集的速度非常慢。

If the result set is not large enough to cause issues such as these then the preferrable technique would be to load the entire result set into the DOM on page load, and then use Javascript to paginate the data in the UI using a show/hide CSS technique.

如果结果集不足以引起诸如此类的问题，则首选的技术是在页面加载时将整个结果集加载到DOM中，然后使用Javascript通过show / hide CSS在UI中对数据进行分页技术。

And that’s all there is to it! If you would like to view and download the full source code for the example described you can do so via this GitHub repo.

这就是全部！如果您想查看和下载所描述示例的完整源代码，可以通过此GitHub存储库进行。

翻译自: https://www.sitepoint.com/pagination-jquery-ajax-php/

相关资源：jQuery+Ajax+PHP无刷新分页

最新回复(0)