How to use database query and result pagination in PHP?

How to use database query and result pagination in PHP?

Introduction:
When doing web development, it is often necessary to query data from the database and display it to the user. When the amount of data is large, in order to improve user experience and reduce server pressure, we usually display the query results in pages. This article will introduce you to how to use database queries and result pagination in PHP, and provide relevant code examples.

1. Database query:
We first need to use PHP to connect to the database and perform query operations.

Code example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

// 执行查询语句
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

// 检查查询结果是否为空
if ($result->num_rows > 0) {
    // 输出每行数据
    while($row = $result->fetch_assoc()) {
        echo "用户名: " . $row["username"]. " - 邮箱: " . $row["email"]. "<br>";
    }
} else {
    echo "没有查询到结果";
}

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

The above code will execute a simple database query and output the user name and email address in the query results.

2. Result paging:
If there are too many query results, we can display the results in paging and display a certain amount of data on each page. Users can switch between different paginations by clicking on the page number on the page.

Code example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

// 每页显示的数据量
$pageSize = 10;
// 当前页码
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// 计算总数据量
$sqlCount = "SELECT COUNT(*) as total FROM users";
$countResult = $conn->query($sqlCount);
$total = $countResult->fetch_assoc()["total"];

// 计算总页数
$totalPages = ceil($total / $pageSize);

// 计算起始索引
$startIndex = ($page - 1) * $pageSize;

// 查询当前页的数据
$sql = "SELECT * FROM users LIMIT $startIndex, $pageSize";
$result = $conn->query($sql);

// 输出当前页的数据
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "用户名: " . $row["username"]. " - 邮箱: " . $row["email"]. "<br>";
    }
} else {
    echo "没有查询到结果";
}

// 输出分页链接
for ($i = 1; $i <= $totalPages; $i++) {
    echo "<a href='?page=$i'>$i</a>";
}

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

The above code will query the user data in the database and display the results in pages. Users can switch between different pages by clicking on pagination links.

Summary:
Using database query and result paging in PHP can easily obtain and display large amounts of data and improve user experience. With the introduction and code examples in this article, you can easily implement this feature. When using PHP to connect to the database and perform paging queries, you need to pay attention to security issues, such as preventing SQL injection. It is recommended to take corresponding protective measures in actual applications.

The above is the detailed content of How to use database query and result pagination in PHP?. For more information, please follow other related articles on the PHP Chinese website!