PHP历_批操SQL 数据库表及字段信息
<link rel="stylesheet" href="/_/_js/layui/css/layui.css">
<?php
session_start();
define('CHAOYI',true);
require $_SERVER['DOCUMENT_ROOT'].'/_/_php/data/database.php';
require ROOT.'_php/data/common.php';
require ROOT.'_php/data/config.php';
// 调试模式
$debugMode = true;
// 分页参数
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 5;
// 错误信息存储
$errors = [];
$tables = [];
$totalTables = 0;
$totalPages = 0;
$allTables = [];
// 检查连接
if ($conn->connect_error) {
$errors[] = "数据库连接失败: " . $conn->connect_error;
} else {
// 设置字符集
if (!$conn->set_charset("utf8")) {
$errors[] = "设置字符集失败: " . $conn->error;
} else {
// 1. 先获取所有表名到数组（兼容所有MySQL版本）
$tablesResult = $conn->query("SHOW TABLES");
if (!$tablesResult) {
$errors[] = "获取表列表失败: " . $conn->error;
} else {
// 将所有表名存入数组
while ($table = $tablesResult->fetch_row()) {
$allTables[] = $table[0];
}
$tablesResult->free();
// 计算总数和总页数
$totalTables = count($allTables);
$totalPages = ceil($totalTables / $perPage);
// 2. 手动进行分页处理
$offset = ($page - 1) * $perPage;
$currentPageTables = array_slice($allTables, $offset, $perPage);
// 3. 处理当前页的表
foreach ($currentPageTables as $tableName) {
$tableData = [
'name' => $tableName,
'fields' => [],
'error' => ''
];
// 获取表的字段信息
$fieldsQuery = "DESCRIBE `$tableName`";
$fieldsResult = $conn->query($fieldsQuery);
if (!$fieldsResult) {
$tableData['error'] = "获取字段失败: " . $conn->error . " (SQL: $fieldsQuery)";
} else {
while ($field = $fieldsResult->fetch_assoc()) {
$tableData['fields'][] = $field;
}
$fieldsResult->free();
}
$tables[] = $tableData;
}
}
// 关闭连接
$conn->close();
}
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<title>数据库表及字段信息</title>
<style>
body {
font-family: Arial, sans-serif;
margin: 20px;
line-height: 1.6;
}
.pagination {
margin: 20px 0;
text-align: center;
}
.pagination a {
display: inline-block;
padding: 8px 16px;
margin: 0 4px;
border: 1px solid #ddd;
text-decoration: none;
color: #333;
}
.pagination a.active {
background-color: #4CAF50;
color: white;
border: 1px solid #4CAF50;
}
.pagination a:hover:not(.active) {
background-color: #ddd;
}
table {
width: 100%;
border-collapse: collapse;
margin-bottom: 30px;
box-shadow: 0 2px 3px rgba(0,0,0,0.1);
}
th, td {
padding: 12px 15px;
text-align: left;
border-bottom: 1px solid #ddd;
}
th {
background-color: #f2f2f2;
font-weight: bold;
}
.table-header {
background-color: #4CAF50;
color: white;
}
.table-name {
font-size: 1.2em;
margin-top: 20px;
color: #2c3e50;
}
.info {
margin: 10px 0;
color: #666;
}
.error {
color: #dc3545;
background-color: #f8d7da;
padding: 10px 15px;
border: 1px solid #f5c6cb;
border-radius: 4px;
margin: 10px 0;
}
.debug {
color: #0c5460;
background-color: #d1ecf1;
padding: 10px 15px;
border: 1px solid #bee5eb;
border-radius: 4px;
margin: 10px 0;
}
</style>
</head>
<body>
<h1>数据库表及字段信息</h1>
<?php if ($debugMode): ?>
<div class="debug">
<strong>调试信息:</strong><br>
数据库: <?php echo htmlspecialchars($dbName); ?><br>
页码: <?php echo $page; ?>, 每页数量: <?php echo $perPage; ?><br>
总表数: <?php echo $totalTables; ?>
</div>
<?php endif; ?>
<?php if (!empty($errors)): ?>
<?php foreach ($errors as $error): ?>
<div class="error"><?php echo htmlspecialchars($error); ?></div>
<?php endforeach; ?>
<?php endif; ?>
<div class="info">
总表数: <?php echo $totalTables; ?> |
当前页: <?php echo $page; ?>/<?php echo $totalPages; ?> |
每页显示: <?php echo $perPage; ?> 个表
</div>
<?php if (!empty($tables)): ?>
<?php foreach ($tables as $table): ?>
<h2 class="table-name">表名: <?php echo htmlspecialchars($table['name']); ?></h2>
<?php if (!empty($table['error'])): ?>
<div class="error"><?php echo htmlspecialchars($table['error']); ?></div>
<?php endif; ?>
<?php if (!empty($table['fields'])): ?>
<table>
<tr class="table-header">
<th>字段名</th>
<th>类型</th>
<th>是否为空</th>
<th>键</th>
<th>默认值</th>
<th>额外信息</th>
</tr>
<?php foreach ($table['fields'] as $field): ?>
<tr>
<td><?php echo htmlspecialchars($field['Field']); ?></td>
<td><?php echo htmlspecialchars($field['Type']); ?></td>
<td><?php echo ($field['Null'] === 'YES' ? '是' : '否'); ?></td>
<td><?php echo htmlspecialchars($field['Key']); ?></td>
<td><?php echo (is_null($field['Default']) ? 'NULL' : htmlspecialchars($field['Default'])); ?></td>
<td><?php echo htmlspecialchars($field['Extra']); ?></td>
</tr>
<?php endforeach; ?>
</table>
<?php else: ?>
<p>该表没有字段信息</p>
<?php endif; ?>
<?php endforeach; ?>
<?php else: ?>
<?php if (empty($errors)): ?>
<p>没有找到任何表</p>
<?php endif; ?>
<?php endif; ?>
<!-- 分页导航 -->
<?php if ($totalPages > 1): ?>
<div class="pagination">
<?php if ($page > 1): ?>
<a href="?page=<?php echo $page - 1; ?>&per_page=<?php echo $perPage; ?>">上一页</a>
<?php endif; ?>
<?php
// 显示页码（只显示当前页附近的页码）
for ($i = max(1, $page - 2); $i <= min($totalPages, $page + 2); $i++) {
if ($i == $page) {
echo '<a href="?page=' . $i . '&per_page=' . $perPage . '" class="active">' . $i . '</a>';
} else {
echo '<a href="?page=' . $i . '&per_page=' . $perPage . '">' . $i . '</a>';
}
}
?>
<?php if ($page < $totalPages): ?>
<a href="?page=<?php echo $page + 1; ?>&per_page=<?php echo $perPage; ?>">下一页</a>
<?php endif; ?>
</div>
<?php endif; ?>
</body>
</html>
效果图：
浙公网安备 33010602011771号