jQuery database implements paging query database
In modern web applications, the database is an indispensable component. In large projects, databases used to store and manage data often contain thousands of records. Therefore, in many cases, loading all data records into a web page is not a good option because it will cause the web page to load very slowly. At this time, we need to learn how to use jQuery database to implement paging query database to achieve better performance and user experience.
In this article, we will introduce methods suitable for most jQuery-based web applications to implement paging queries of the database. We will cover the following topics:
1. How to set up the database information and jQuery library so that it works for your application.
2. How to write code to retrieve the required data from the database.
3. How to implement paging function by using jQuery to improve page response speed and performance.
- Set up the environment
First, you need to create a PHP file containing database connection information. Here, we will define the host name, user name, password and database name of the database to connect to the database. It looks like this:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>Copy after login
In this file, you need to change the variables $servername, $username, $password and $ dbname to match the access requirements of your database.
In order to implement the paging function, you need to create a table named products in the database and add some records to it for paging query. You can use the following SQL code to create this table:
CREATE TABLE products (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(30) NOT NULL,
product_description VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Copy after login
- Writing the Code
In this section, we will write our code to retrieve data from the database, and Split data into multiple pages to implement paging functionality.
First, we will create a new file called config.php to contain our database connection information. We will then write a page called index.php which will be our home page where we can view a list of all our products.
The following is the complete code for index.php:
<?php
include 'config.php';
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>我的产品目录</title>
<link rel="stylesheet" href="css/styles.css">
</head>
<body>
<div id="container">
<h1>我的产品目录</h1>
<table>
<thead>
<tr>
<th>ID</th>
<th>产品名称</th>
<th>产品描述</th>
<th>注册日期</th>
</tr>
</thead>
<tbody>
<?php
// 将结果分页
$results_per_page = 5;
if (!isset($_GET['page'])) {
$page = 1;
} else {
$page = $_GET['page'];
}
$sql = "SELECT * FROM products";
$result = $conn->query($sql);
$number_of_results = mysqli_num_rows($result);
$number_of_pages = ceil($number_of_results/$results_per_page);
$this_page_first_result = ($page-1)*$results_per_page;
$sql = "SELECT * FROM products LIMIT " . $this_page_first_result . ',' . $results_per_page;
$result = $conn->query($sql);
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['product_name']; ?></td>
<td><?php echo $row['product_description']; ?></td>
<td><?php echo $row['reg_date']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
for ($page=1;$page<=$number_of_pages;$page++) {
echo '<a href="index.php?page=' . $page . '">' . $page . '</a> ';
}
?>
</div>
</body>
</html>Copy after login
In the above code, we first include our database connection information and define The $results_per_page variable indicates the number of records to be displayed on each page. We then check if the page statement exists in the $_GET variable, and if not, set it to 1. Then, we use SELECT * FROM products to query all products, and then use the mysqli_num_rows() function to count the number of records, which is used to calculate paging. After that, we define the $number_of_pages variable to represent the total number of pages available and in the next step calculate $this_page_first_result with a specific page number. Then, we use the LIMIT clause to query a specific result set and write it to the table.
Finally, we create a loop that generates links to available pages and places them at the bottom of the page so that the user can select the desired page number.
- Implement paging query
Now, our page can display all products and support simple paging function. However, on larger projects, we may need to be more efficient in this regard. Below, we will implement the new pagination functionality using the jQuery library.
In order to achieve this, we need to create a new file called ajax.php, meaning we will use technologies such as AJAX, PHP and jQuery libraries. Here is the code for ajax.php:
<?php
include 'config.php';
if (isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
}
$results_per_page = 5;
$this_page_first_result = ($page-1)*$results_per_page;
$sql = "SELECT * FROM products LIMIT " . $this_page_first_result . ',' . $results_per_page;
$result = $conn->query($sql);
$data = [];
while($row = mysqli_fetch_array($result)) {
array_push($data, $row);
}
$conn->close();
echo json_encode($data);
?>Copy after login
In the above code, we first include our database connection information and check if it exists in the $_GET variable page statement, if present, stores it in the $page variable, otherwise sets it to 1. Then, we define the $results_per_page variable and the $this_page_first_result variable to limit the set of retrieved results. Finally, we use SELECT * FROM products LIMIT to query a specific result set and store it as data output in JSON format.
In order to use AJAX requests to get data in the front-end page, we will create a new file called script.js to handle these requests. The following is the code of script.js:
$(document).ready(function() {
var resultsPerPage = 5;
var currentPage = 1;
function getProducts() {
$.ajax({
url: 'ajax.php',
type: 'GET',
data: {page: currentPage},
dataType: 'json',
success: function(data) {
displayProducts(data);
displayPagination();
}
});
}
function displayProducts(products) {
var html = '';
$.each(products, function(index, product) {
html += '<tr>';
html += '<td>' + product.id + '</td>';
html += '<td>' + product.product_name + '</td>';
html += '<td>' + product.product_description + '</td>';
html += '<td>' + product.reg_date + '</td>';
html += '</tr>';
});
$('table tbody').html(html);
}
function displayPagination() {
var html = '';
for (var i = 1; i <= totalPages(); i++) {
html += '<a href="#" data-page="' + i + '">' + i + '</a> ';
}
$('.pagination').html(html);
$('.pagination a').click(function(event) {
event.preventDefault();
currentPage = $(this).data('page');
getProducts();
});
}
function totalPages() {
return Math.ceil(totalProducts() / resultsPerPage);
}
function totalProducts() {
return $('.total-products').data('total');
}
getProducts();
});Copy after login
在上述代码中，我们首先定义 $resultsPerPage 和 $currentPage 变量，用于存储每页显示的记录数和当前显示的页码。然后，我们定义一个名为 getProducts() 的函数，用于从 ajax.php 中请求数据，并在成功时将回调处理另外两个函数，即 displayProducts() 和 displayPagination()。在 displayProducts() 函数中，我们使用 jQuery 逐个迭代数据中的每个产品，为其创建一个 HTML 表格行。在 displayPagination() 函数中，我们使用一个简单的循环来创建页面链接，并将其添加进 .pagination 位于页面底部的导航原素中。紧接着，我们使用 .pagination a 绑定一个单击事件，当点击链接时将更新 $currentPage 变量，并再次调用 getProducts() 函数。
最后，为了在我们的 HTML 页面中使用 script.js 文件，我们需要在 HTML 底部的 </body> 标记前添加以下代码：
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="js/script.js"></script>
Copy after login
这些代码用于下载包括 jQuery 库在内的其他脚本文件。
结论
通过本文所介绍的技术，你可以使用 jQuery 数据库实现分页查询，而无需加载所有记录。这可以提高页面的加载速度和响应速度，从而提高用户体验和性能。虽然本文只是展示了一种简单的技术，但是随着你了解更多的方法和技术，可以制定更健壮的和专业的 web 应用程序。
The above is the detailed content of How to use jQuery database to implement paging query database. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
