PHP-分页显示

360M2 · 发表于昨天 13:27

<?php
// config.php - 数据库配置
$db_config = [
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'name' => 'testdb',
'table' => 'products' // 要显示的表名
];
// 连接数据库
function connectDB($config) {
$conn = new mysqli($config['host'], $config['user'], $config['pass'], $config['name']);
if ($conn->connect_error) {
die("数据库连接失败: " . $conn->connect_error);
}
$conn->set_charset("utf8mb4");
return $conn;
}
// 获取总记录数
function getTotalRecords($conn, $table) {
$sql = "SELECT COUNT(*) as total FROM `$table`";
$result = $conn->query($sql);
if ($result && $result->num_rows > 0) {
$row = $result->fetch_assoc();
return $row['total'];
}
return 0;
}
// 获取当前页数据
function getPageData($conn, $table, $page, $itemsPerPage) {
$offset = ($page - 1) * $itemsPerPage;
// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("SELECT * FROM `$table` LIMIT ? OFFSET ?");
$stmt->bind_param("ii", $itemsPerPage, $offset);
$stmt->execute();
$result = $stmt->get_result();
$data = [];
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
}
$stmt->close();
return $data;
}
// 获取表结构（列名）
function getTableColumns($conn, $table) {
$sql = "DESCRIBE `$table`";
$result = $conn->query($sql);
$columns = [];
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$columns[] = $row['Field'];
}
}
return $columns;
}
// 生成分页链接
function generatePagination($totalPages, $currentPage, $baseUrl = '') {
if ($totalPages <= 1) return '';
$pagination = '<nav aria-label="Page navigation"><ul class="pagination justify-content-center">';
// 上一页
if ($currentPage > 1) {
$pagination .= '<li class="page-item"><a class="page-link" href="' . $baseUrl . 'page=' . ($currentPage - 1) . '">« 上一页</a></li>';
} else {
$pagination .= '<li class="page-item disabled"><span class="page-link">« 上一页</span></li>';
}
// 页码范围
$startPage = max(1, $currentPage - 2);
$endPage = min($totalPages, $currentPage + 2);
// 第一页
if ($startPage > 1) {
$pagination .= '<li class="page-item"><a class="page-link" href="' . $baseUrl . 'page=1">1</a></li>';
if ($startPage > 2) {
$pagination .= '<li class="page-item disabled"><span class="page-link">...</span></li>';
}
}
// 中间页码
for ($i = $startPage; $i <= $endPage; $i++) {
if ($i == $currentPage) {
$pagination .= '<li class="page-item active"><span class="page-link">' . $i . '</span></li>';
} else {
$pagination .= '<li class="page-item"><a class="page-link" href="' . $baseUrl . 'page=' . $i . '">' . $i . '</a></li>';
}
}
// 最后一页
if ($endPage < $totalPages) {
if ($endPage < $totalPages - 1) {
$pagination .= '<li class="page-item disabled"><span class="page-link">...</span></li>';
}
$pagination .= '<li class="page-item"><a class="page-link" href="' . $baseUrl . 'page=' . $totalPages . '">' . $totalPages . '</a></li>';
}
// 下一页
if ($currentPage < $totalPages) {
$pagination .= '<li class="page-item"><a class="page-link" href="' . $baseUrl . 'page=' . ($currentPage + 1) . '">下一页 »</a></li>';
} else {
$pagination .= '<li class="page-item disabled"><span class="page-link">下一页 »</span></li>';
}
$pagination .= '</ul></nav>';
return $pagination;
}
// 处理分页参数
$itemsPerPage = isset($_GET['perpage']) ? intval($_GET['perpage']) : 10;
$itemsPerPage = in_array($itemsPerPage, [5, 10, 20, 50, 100]) ? $itemsPerPage : 10;
$currentPage = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
// 连接数据库
$conn = connectDB($db_config);
// 获取总记录数
$totalRecords = getTotalRecords($conn, $db_config['table']);
// 计算总页数
$totalPages = ceil($totalRecords / $itemsPerPage);
// 确保当前页不超过总页数
if ($totalPages > 0 && $currentPage > $totalPages) {
$currentPage = $totalPages;
} elseif ($totalPages == 0) {
$currentPage = 1;
}
// 获取当前页数据
$data = getPageData($conn, $db_config['table'], $currentPage, $itemsPerPage);
// 获取表结构
$columns = getTableColumns($conn, $db_config['table']);
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>MySQL数据分页管理系统</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css">
<style>
:root {
--primary-color: #4361ee;
--secondary-color: #3a0ca3;
--success-color: #4cc9f0;
--light-bg: #f8f9fa;
--dark-bg: #212529;
}
body {
background-color: #f8f9fa;
font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
}
.navbar-brand {
font-weight: 600;
}
.card {
border: none;
box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
border-radius: 12px;
overflow: hidden;
margin-bottom: 20px;
}
.card-header {
background-color: var(--primary-color);
color: white;
font-weight: 600;
padding: 15px 20px;
border-bottom: none;
}
.stat-card {
border-radius: 10px;
transition: transform 0.3s ease;
}
.stat-card:hover {
transform: translateY(-5px);
}
.stat-icon {
font-size: 2.5rem;
opacity: 0.8;
}
.data-table {
background-color: white;
}
.data-table thead th {
background-color: var(--light-bg);
border-bottom: 2px solid #dee2e6;
font-weight: 600;
color: var(--dark-bg);
padding: 12px 15px;
}
.data-table tbody tr:hover {
background-color: rgba(67, 97, 238, 0.05);
}
.table-responsive {
border-radius: 8px;
overflow: hidden;
}
.pagination .page-item.active .page-link {
background-color: var(--primary-color);
border-color: var(--primary-color);
}
.pagination .page-link {
color: var(--primary-color);
border: 1px solid #dee2e6;
}
.pagination .page-link:hover {
background-color: rgba(67, 97, 238, 0.1);
color: var(--secondary-color);
}
.badge-custom {
background-color: var(--primary-color);
color: white;
font-weight: 500;
padding: 5px 10px;
}
.btn-primary {
background-color: var(--primary-color);
border-color: var(--primary-color);
}
.btn-primary:hover {
background-color: var(--secondary-color);
border-color: var(--secondary-color);
}
.page-size-selector {
max-width: 120px;
}
.search-box {
max-width: 300px;
}
footer {
background-color: var(--dark-bg);
color: white;
}
.loading-spinner {
display: none;
text-align: center;
padding: 20px;
}
@media (max-width: 768px) {
.stat-card {
margin-bottom: 15px;
}
.page-size-selector, .search-box {
width: 100% !important;
max-width: 100% !important;
margin-bottom: 10px;
}
.table-responsive {
font-size: 14px;
}
}
</style>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark" style="background-color: var(--primary-color);">
<div class="container-fluid">
<a class="navbar-brand" href="#">
<i class="bi bi-database me-2"></i>数据管理系统
</a>
<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ms-auto">
<li class="nav-item">
<a class="nav-link active" href="#"><i class="bi bi-house-door me-1"></i>首页</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#"><i class="bi bi-gear me-1"></i>设置</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#"><i class="bi bi-question-circle me-1"></i>帮助</a>
</li>
</ul>
</div>
</div>
</nav>
<div class="container-fluid mt-4">
<div class="row">
<div class="col-12">
<div class="mb-4">
<h2 class="fw-bold text-dark">
<i class="bi bi-table me-2"></i>数据表: <?php echo htmlspecialchars($db_config['table']); ?>
</h2>
<p class="text-muted">分页显示数据库表中的所有记录</p>
</div>
<div class="row mb-4">
<div class="col-md-3 col-sm-6">
<div class="card stat-card border-left-primary">
<div class="card-body">
<div class="d-flex justify-content-between align-items-center">
<div>
<h6 class="text-uppercase text-muted mb-2">总记录数</h6>
<h3 class="mb-0"><?php echo $totalRecords; ?></h3>
</div>
<div class="stat-icon text-primary">
<i class="bi bi-clipboard-data"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="card stat-card border-left-success">
<div class="card-body">
<div class="d-flex justify-content-between align-items-center">
<div>
<h6 class="text-uppercase text-muted mb-2">总页数</h6>
<h3 class="mb-0"><?php echo $totalPages; ?></h3>
</div>
<div class="stat-icon text-success">
<i class="bi bi-file-earmark-text"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="card stat-card border-left-info">
<div class="card-body">
<div class="d-flex justify-content-between align-items-center">
<div>
<h6 class="text-uppercase text-muted mb-2">当前页码</h6>
<h3 class="mb-0"><?php echo $currentPage; ?></h3>
</div>
<div class="stat-icon text-info">
<i class="bi bi-123"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col-md-3 col-sm-6">
<div class="card stat-card border-left-warning">
<div class="card-body">
<div class="d-flex justify-content-between align-items-center">
<div>
<h6 class="text-uppercase text-muted mb-2">表字段数</h6>
<h3 class="mb-0"><?php echo count($columns); ?></h3>
</div>
<div class="stat-icon text-warning">
<i class="bi bi-columns-gap"></i>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="card mb-4">
<div class="card-header d-flex justify-content-between align-items-center">
<h5 class="mb-0">数据控制面板</h5>
<div class="d-flex">
<form method="get" class="d-flex me-2 search-box">
<input type="text" class="form-control" placeholder="搜索..." aria-label="搜索">
<button class="btn btn-outline-primary ms-2" type="submit">
<i class="bi bi-search"></i>
</button>
</form>
<form method="get" class="d-flex page-size-selector">
<select class="form-select" name="perpage" onchange="this.form.submit()">
<option value="5" <?php echo $itemsPerPage == 5 ? 'selected' : ''; ?>>5 条/页</option>
<option value="10" <?php echo $itemsPerPage == 10 ? 'selected' : ''; ?>>10 条/页</option>
<option value="20" <?php echo $itemsPerPage == 20 ? 'selected' : ''; ?>>20 条/页</option>
<option value="50" <?php echo $itemsPerPage == 50 ? 'selected' : ''; ?>>50 条/页</option>
<option value="100" <?php echo $itemsPerPage == 100 ? 'selected' : ''; ?>>100 条/页</option>
</select>
<?php if(isset($_GET['page'])): ?>
<input type="hidden" name="page" value="<?php echo $currentPage; ?>">
<?php endif; ?>
</form>
</div>
</div>
</div>
<div class="card">
<div class="card-header d-flex justify-content-between align-items-center">
<h5 class="mb-0">数据列表</h5>
<span class="badge badge-custom">第 <?php echo $currentPage; ?> 页 / 共 <?php echo $totalPages; ?> 页</span>
</div>
<div class="card-body p-0">
<?php if (!empty($data)): ?>
<div class="table-responsive">
<table class="table table-hover mb-0 data-table">
<thead>
<tr>
<?php foreach ($columns as $index => $column): ?>
<th>
<div class="d-flex align-items-center">
<?php echo htmlspecialchars($column); ?>
<?php if($index == 0): ?>
<i class="bi bi-key ms-1 text-warning" title="主键"></i>
<?php endif; ?>
</div>
</th>
<?php endforeach; ?>
<th>操作</th>
</tr>
</thead>
<tbody>
<?php foreach ($data as $row): ?>
<tr>
<?php foreach ($columns as $column): ?>
<td>
<?php
$value = isset($row[$column]) ? $row[$column] : '';
// 如果是日期时间格式，格式化显示
if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $value)) {
echo date('Y-m-d H:i', strtotime($value));
} elseif (is_numeric($value) && strpos($column, 'price') !== false) {
echo '¥' . number_format($value, 2);
} else {
echo htmlspecialchars($value);
}
?>
</td>
<?php endforeach; ?>
<td>
<button class="btn btn-sm btn-outline-primary me-1" title="查看">
<i class="bi bi-eye"></i>
</button>
<button class="btn btn-sm btn-outline-success me-1" title="编辑">
<i class="bi bi-pencil"></i>
</button>
<button class="btn btn-sm btn-outline-danger" title="删除">
<i class="bi bi-trash"></i>
</button>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php else: ?>
<div class="text-center py-5">
<i class="bi bi-inbox display-1 text-muted mb-3"></i>
<h4 class="text-muted"><?php echo $totalRecords > 0 ? '没有找到当前页的数据' : '表中暂无数据'; ?></h4>
<p class="text-muted mb-4">请检查数据库连接和表名配置</p>
<a href="?" class="btn btn-primary">
<i class="bi bi-arrow-clockwise me-2"></i>刷新页面
</a>
</div>
<?php endif; ?>
</div>
<?php if ($totalPages > 1): ?>
<div class="card-footer">
<div class="d-flex justify-content-between align-items-center">
<div>
<span class="text-muted">
显示第 <?php echo (($currentPage - 1) * $itemsPerPage) + 1; ?>
到 <?php echo min($currentPage * $itemsPerPage, $totalRecords); ?> 条记录，
共 <?php echo $totalRecords; ?> 条
</span>
</div>
<div>
<?php
$baseUrl = !empty($_GET) ? '?' . http_build_query(array_diff_key($_GET, ['page' => ''])) . '&' : '?';
if (strpos($baseUrl, '?') === 0 && $baseUrl !== '?') {
$baseUrl = substr($baseUrl, 1);
}
echo generatePagination($totalPages, $currentPage, $baseUrl);
?>
</div>
</div>
</div>
<?php endif; ?>
</div>
<div class="card mt-4">
<div class="card-header">
<h5 class="mb-0"><i class="bi bi-gear me-2"></i>配置信息</h5>
</div>
<div class="card-body">
<div class="row">
<div class="col-md-6">
<ul class="list-group list-group-flush">
<li class="list-group-item d-flex justify-content-between">
<span>数据库主机</span>
<span class="text-primary"><?php echo $db_config['host']; ?></span>
</li>
<li class="list-group-item d-flex justify-content-between">
<span>数据库名称</span>
<span class="text-primary"><?php echo $db_config['name']; ?></span>
</li>
<li class="list-group-item d-flex justify-content-between">
<span>表名称</span>
<span class="text-primary"><?php echo $db_config['table']; ?></span>
</li>
</ul>
</div>
<div class="col-md-6">
<ul class="list-group list-group-flush">
<li class="list-group-item d-flex justify-content-between">
<span>每页显示</span>
<span class="badge bg-primary rounded-pill"><?php echo $itemsPerPage; ?> 条</span>
</li>
<li class="list-group-item d-flex justify-content-between">
<span>PHP版本</span>
<span><?php echo phpversion(); ?></span>
</li>
<li class="list-group-item d-flex justify-content-between">
<span>MySQL版本</span>
<span><?php echo $conn->server_info; ?></span>
</li>
</ul>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<footer class="mt-5 py-4">
<div class="container">
<div class="row">
<div class="col-md-6 text-center text-md-start">
</div>
<div class="col-md-6 text-center text-md-end">
<p class="mb-0">
<i class="bi bi-lightning-charge-fill text-warning me-1"></i>
当前时间: <?php echo date('Y-m-d H:i:s'); ?>
</p>
</div>
</div>
</div>
</footer>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
// 简单的交互效果
document.addEventListener('DOMContentLoaded', function() {
// 表格行点击效果
const tableRows = document.querySelectorAll('.data-table tbody tr');
tableRows.forEach(row => {
row.addEventListener('click', function(e) {
if (!e.target.closest('button')) {
this.classList.toggle('table-active');
}
});
});
// 加载动画模拟
const refreshBtn = document.querySelector('.btn-primary[href="?"]');
if (refreshBtn) {
refreshBtn.addEventListener('click', function(e) {
e.preventDefault();
this.innerHTML = '<span class="spinner-border spinner-border-sm me-2" role="status" aria-hidden="true"></span>加载中...';
setTimeout(() => {
window.location.href = '?';
}, 500);
});
}
// 删除按钮确认
const deleteBtns = document.querySelectorAll('.btn-outline-danger');
deleteBtns.forEach(btn => {
btn.addEventListener('click', function() {
if (confirm('确定要删除这条记录吗？')) {
this.innerHTML = '<span class="spinner-border spinner-border-sm" role="status" aria-hidden="true"></span>';
setTimeout(() => {
alert('删除功能需要后端API支持，当前为演示界面');
this.innerHTML = '<i class="bi bi-trash"></i>';
}, 1000);
}
});
});
});
</script>
<?php
// 关闭数据库连接
if (isset($conn)) {
$conn->close();
}
?>
</body>
</html>

复制代码

		自动登录	找回密码
密码			立即注册