PHP 数据分页与搜索功能实现

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

<?php
// 数据库连接
$conn = new mysqli("localhost", "username", "password", "database");
if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}

// 分页参数
$limit = 10; // 每页显示的记录数
$page = isset($_GET['page']) ? intval($_GET['page']) : 1; // 当前页码
$offset = ($page - 1) * $limit; // 计算偏移量

// 获取总记录数
$result = $conn->query("SELECT COUNT(*) AS total FROM products");
$total = $result->fetch_assoc()['total'];

// 计算总页数
$total_pages = ceil($total / $limit);

// 查询当前页的数据
$sql = "SELECT * FROM products LIMIT $offset, $limit";
$data = $conn->query($sql);

// 输出数据
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Price</th></tr>";
while ($row = $data->fetch_assoc()) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['price']}</td></tr>";
}
echo "</table>";

// 分页导航
echo "<div>";
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}
echo "</div>";

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

<?php
// 数据库连接
$conn = new mysqli("localhost", "username", "password", "database");
if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}

// 获取搜索关键字
$search = isset($_GET['search']) ? $conn->real_escape_string($_GET['search']) : '';

// 分页参数
$limit = 10;
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$offset = ($page - 1) * $limit;

// 获取总记录数
$sql = "SELECT COUNT(*) AS total FROM products WHERE name LIKE '%$search%'";
$result = $conn->query($sql);
$total = $result->fetch_assoc()['total'];
$total_pages = ceil($total / $limit);

// 查询当前页的数据
$sql = "SELECT * FROM products WHERE name LIKE '%$search%' LIMIT $offset, $limit";
$data = $conn->query($sql);

// 输出数据
echo "<form method='GET'>";
echo "搜索：<input type='text' name='search' value='$search'>";
echo "<button type='submit'>搜索</button>";
echo "</form>";

echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Price</th></tr>";
while ($row = $data->fetch_assoc()) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['price']}</td></tr>";
}
echo "</table>";

// 分页导航
echo "<div>";
for ($i = 1; $i <= $total_pages; $i++) {
    $query = http_build_query(['page' => $i, 'search' => $search]);
    echo "<a href='?$query'>$i</a> ";
}
echo "</div>";

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

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">

<div class="container mt-5">
    <form method="GET" class="mb-3">
        <div class="input-group">
            <input type="text" name="search" class="form-control" placeholder="搜索商品" value="<?php echo $search; ?>">
            <button class="btn btn-primary">搜索</button>
        </div>
    </form>

    <table class="table table-bordered">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Price</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = $data->fetch_assoc()): ?>
                <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['price']; ?></td>
                </tr>
            <?php endwhile; ?>
        </tbody>
    </table>

    <nav>
        <ul class="pagination">
            <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                <li class="page-item <?php if ($i == $page) echo 'active'; ?>">
                    <a class="page-link" href="?<?php echo http_build_query(['page' => $i, 'search' => $search]); ?>"><?php echo $i; ?></a>
                </li>
            <?php endfor; ?>
        </ul>
    </nav>
</div>