php实现分页增删改差

PHP 实现分页增删改查

数据库连接

创建 config.php 文件存储数据库连接信息：

<?php
$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

分页查询

创建 list.php 实现分页逻辑：

<?php
require 'config.php';

$perPage = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$total = $stmt->fetchColumn();
$pages = ceil($total / $perPage);

$sql = "SELECT * FROM users LIMIT $offset, $perPage";
$users = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
?>

<table>
    <?php foreach ($users as $user): ?>
    <tr>
        <td><?= htmlspecialchars($user['name']) ?></td>
        <td>
            <a href="edit.php?id=<?= $user['id'] ?>">Edit</a>
            <a href="delete.php?id=<?= $user['id'] ?>" onclick="return confirm('Confirm?')">Delete</a>
        </td>
    </tr>
    <?php endforeach ?>
</table>

<div class="pagination">
    <?php for ($i = 1; $i <= $pages; $i++): ?>
    <a href="?page=<?= $i ?>" <?= $i == $page ? 'class="active"' : '' ?>><?= $i ?></a>
    <?php endfor ?>
</div>

新增数据

创建 add.php 处理数据添加：

<?php
require 'config.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];

    $sql = "INSERT INTO users (name, email) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$name, $email]);

    header("Location: list.php");
    exit;
}
?>

<form method="post">
    <input type="text" name="name" placeholder="Name" required>
    <input type="email" name="email" placeholder="Email" required>
    <button type="submit">Add</button>
</form>

修改数据

创建 edit.php 实现数据更新：

<?php
require 'config.php';

$id = $_GET['id'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch();

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];

    $sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$name, $email, $id]);

    header("Location: list.php");
    exit;
}
?>

<form method="post">
    <input type="text" name="name" value="<?= htmlspecialchars($user['name']) ?>" required>
    <input type="email" name="email" value="<?= htmlspecialchars($user['email']) ?>" required>
    <button type="submit">Update</button>
</form>

删除数据

创建 delete.php 处理删除操作：

<?php
require 'config.php';

$id = $_GET['id'];
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$id]);

header("Location: list.php");
exit;
?>

样式优化

添加基础CSS样式到页面头部：

<style>
.pagination a {
    display: inline-block;
    padding: 5px 10px;
    margin: 0 2px;
    border: 1px solid #ddd;
}
.pagination a.active {
    background: #4CAF50;
    color: white;
}
table {
    width: 100%;
    border-collapse: collapse;
}
table, th, td {
    border: 1px solid #ddd;
    padding: 8px;
}
</style>

安全注意事项

所有用户输入必须过滤：

$name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_STRING);
$email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL);

使用预处理语句防止SQL注入：

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([':name' => $name, ':email' => $email]);