PHP历_批操SQL 数据库表及字段信息3
<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';
// 直接获取配置中的数据库名
$dbName = $database_config['dbname'] ?? '';
// 固定分页参数，减少动态判断
$page = (int)($_GET['page'] ?? 1);
$perPage = (int)($_GET['per_page'] ?? 10);
$errors = [];
$tables = [];
$allTables = [];
// 获取所有表名及备注
$tablesResult = $conn->query("SHOW TABLES");
while ($table = $tablesResult->fetch_row()) {
$tableName = $table[0];
$tableComment = '';
// 直接获取表状态，不额外判断结果
$statusResult = $conn->query("SHOW TABLE STATUS LIKE '" . $conn->real_escape_string($tableName) . "'");
$statusData = $statusResult->fetch_assoc();
$tableComment = $statusData['Comment'] ?? '';
$statusResult->free();
$allTables[] = ['name' => $tableName, 'comment' => $tableComment];
}
$tablesResult->free();
// 分页计算
$totalTables = count($allTables);
$totalPages = ceil($totalTables / $perPage);
$offset = ($page - 1) * $perPage;
$currentPageTables = array_slice($allTables, $offset, $perPage);
// 处理当前页表字段
foreach ($currentPageTables as $tableInfo) {
$tableName = $tableInfo['name'];
$tableData = [
'name' => $tableName,
'comment' => $tableInfo['comment'],
'fields' => [],
'error' => ''
];
// 只使用SHOW FULL COLUMNS获取字段，去掉备用方法
$fieldsQuery = "SHOW FULL COLUMNS FROM `" . $conn->real_escape_string($tableName) . "`";
$fieldsResult = $conn->query($fieldsQuery);
if (!$fieldsResult) {
$tableData['error'] = "获取字段失败: " . $conn->error;
} else {
while ($field = $fieldsResult->fetch_assoc()) {
$tableData['fields'][] = [
'Field' => $field['Field'],
'Type' => $field['Type'],
'Null' => $field['Null'],
'Key' => $field['Key'],
'Default' => $field['Default'],
'Extra' => $field['Extra'],
'Comment' => $field['Comment'] ?? ''
];
}
$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: 30px;color: #2c3e50;}
.table-comment {color: #666;font-style: italic;margin: 5px 0 15px 0;padding: 8px;background-color: #f9f9f9;border-left: 3px solid #4CAF50;}
.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;}
.comment-column {width: 200px;}
</style>
</head>
<body>
<h1>数据库表及字段信息</h1>
<!-- 只显示关键错误 -->
<?php foreach ($errors as $error): ?>
<div class="error"><?php echo htmlspecialchars($error); ?></div>
<?php endforeach; ?>
<!-- 信息统计 -->
<div class="info">
总表数: <?php echo $totalTables ?? 0; ?> |
当前页: <?php echo $page; ?>/<?php echo $totalPages ?? 1; ?> |
每页显示: <?php echo $perPage; ?> 个表
</div>
<!-- 表信息输出 -->
<?php foreach ($tables as $table): ?>
<h2 class="table-name">表名: <?php echo htmlspecialchars($table['name']); ?></h2>
<!-- 表备注直接输出 -->
<div class="table-comment">表备注: <?php echo htmlspecialchars($table['comment'] ?: '无'); ?></div>
<!-- 字段错误显示 -->
<?php if ($table['error']): ?>
<div class="error"><?php echo htmlspecialchars($table['error']); ?></div>
<?php endif; ?>
<!-- 字段表格 -->
<table>
<tr class="table-header">
<th>字段名</th>
<th>类型</th>
<th>是否为空</th>
<th>键</th>
<th>默认值</th>
<th>额外信息</th>
<th class="comment-column">备注</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>
<td><?php echo htmlspecialchars($field['Comment'] ?: '无'); ?></td>
</tr>
<?php endforeach; ?>
</table>
<?php endforeach; ?>
<!-- 无表时提示 -->
<?php if (empty($tables) && empty($errors)): ?>
<p>没有找到任何表</p>
<?php endif; ?>
<!-- 分页 -->
<?php if (($totalPages ?? 1) > 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++): ?>
<a href="?page=<?php echo $i; ?>&per_page=<?php echo $perPage; ?>" <?php echo $i == $page ? 'class="active"' : ''; ?>>
<?php echo $i; ?>
</a>
<?php endfor; ?>
<?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号