How to use database query and result paging function to realize paging display and page navigation of data in PHP?

How to use database query and result paging function to realize paging display and page navigation of data in PHP?

With the rapid development of the Internet, we often need to display a large amount of data on the website and provide page-turning navigation functions to facilitate users to view data on different pages. In PHP, we can achieve this functionality by using database queries and result paging functions. This article will introduce how to use PHP for paging display and page navigation of data.

First, we need to connect to the database and execute the query statement to obtain all the data. Next, we need to calculate the total data volume and the total number of pages. Then, we define the amount of data displayed on each page and calculate the starting position of the data on the current page. Finally, we use the LIMIT clause and OFFSET clause to extract the data of the current page from the database.

The following is a sample code that demonstrates how to implement paging display and page navigation of data in PHP:

<?php
// 设置数据库连接参数
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "myDB";

// 创建数据库连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 定义每页显示的数据数量
$limit = 10;

// 获取当前页数
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// 计算数据起始位置
$start = ($page - 1) * $limit;

// 查询总数据量
$total_query = "SELECT COUNT(*) as total FROM myTable";
$total_result = $conn->query($total_query);
$total_row = $total_result->fetch_assoc();
$total = $total_row['total'];

// 计算总页面数量
$pages = ceil($total / $limit);

// 查询当前页面的数据
$query = "SELECT * FROM myTable LIMIT $start, $limit";
$result = $conn->query($query);

// 显示数据
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "没有数据";
}

// 显示翻页导航
echo "<div class='pagination'>";
if ($page > 1) {
    echo "<a href='?page=1'>首页</a>";
    echo "<a href='?page=" . ($page - 1) . "'>上一页</a>";
}
for ($i = 1; $i <= $pages; $i++) {
    echo "<a href='?page=" . $i . "'>" . $i . "</a>";
}
if ($page < $pages) {
    echo "<a href='?page=" . ($page + 1) . "'>下一页</a>";
    echo "<a href='?page=" . $pages . "'>尾页</a>";
}
echo "</div>";

// 关闭数据库连接
$conn->close();
?>

The above is how to use PHP to implement paging display and page navigation of data. Sample code. With this code, we can easily display large amounts of data in the website and provide user-friendly navigation. hope it is of help to you!

The above is the detailed content of How to use database query and result paging function to realize paging display and page navigation of data in PHP?. For more information, please follow other related articles on the PHP Chinese website!