How to use PHP database connection to implement paging query

How to use PHP database connection to implement paging query

In developing web applications, it often involves the need to query the database and perform paging display. As a commonly used server-side scripting language, PHP has powerful database connection functions and can easily implement paging queries. This article will introduce in detail how to use PHP database connection to implement paging query, and attach corresponding code examples.

1. Prepare the database

Before we start, we need to prepare a database containing the data to be queried. Taking the MySQL database as an example, assume that we have a table named "products", which contains product-related information, including product ID, name, price, etc.

2. Establishing a database connection

First, we need to establish a connection to the database. In PHP, you can use extensions such as mysqli or PDO to implement database connections. Taking mysqli as an example, the example is as follows:

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

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

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
?>

Please replace "localhost", "root", "" and "your_database_name" with the actual database server address, user name, password and database name.

3. Implementing paging query

Below we will take displaying 10 pieces of data on each page as an example to implement the paging query function.

<?php
$limit = 10; // 每页显示的数量
$page = isset($_GET['page']) ? $_GET['page'] : 1; // 当前页码，默认为1

$start = ($page - 1) * $limit; // 分页查询的起始位置

$sql = "SELECT * FROM products LIMIT $start, $limit";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - 名称: " . $row["name"]. " - 价格: " . $row["price"]. "<br>";
    }
} else {
    echo "0 结果";
}

$conn->close();
?>

In the above code, first get the page number of the current page, calculate the starting position of the paging query, and then use "SELECT * FROM products LIMIT $start, $limit" to execute the paging query. If the query result is not empty, iterate over the output data. Finally close the database connection.

4. Implement paging navigation

In order to provide a more friendly user experience, we can also implement paging navigation function so that users can easily switch to other pages.

<?php
// 查询总数据量
$sql = "SELECT COUNT(*) AS total FROM products";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_records = $row['total'];

$total_pages = ceil($total_records / $limit); // 总页数

$prev_page = max(1, $page - 1); // 上一页
$next_page = min($total_pages, $page + 1); // 下一页

echo "<a href='?page=".$prev_page."'>上一页</a> ";

for ($i=1; $i<=$total_pages; $i++) {
    echo "<a href='?page=".$i."'".($i==$page ? " class='current'" : "").">".$i."</a> ";
}

echo "<a href='?page=".$next_page."'>下一页</a>";
?>

In the above code, the total amount of data is first queried and the total number of pages is calculated. Then generate the HTML code for paging navigation based on the current page number and the total number of pages. Users can click on the corresponding page number to switch to other pages, and the page number of the current page will be displayed in different styles.

Summary:

Through the above steps, we can use PHP database connection to implement paging query function. First establish a database connection, then perform paging queries according to user needs, and finally implement paging navigation to provide a more friendly user experience. This method is not only applicable to MySQL database, but other commonly used databases, such as Oracle, SQLite, etc., can also use corresponding extensions to achieve similar functions.

The above is the detailed content of How to use PHP database connection to implement paging query. For more information, please follow other related articles on the PHP Chinese website!