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]);
