在使用pbootcms的时候，很多时候会用到数据库，但是pb的数据库是.db文件，所以就很麻烦。
没有现成的可视化页面可以查看数据库，因此，这里开发了一个php在线查看的php可视化页面。
这样会方便许多。
如何使用？
在db文件的文件夹中，创建一个php，将下面代码放进去，修改自己的数据库名称，然后访问php文件即可使用。
已改进，可以查看多个db数据库
<?php
// 扫描当前目录下的所有.db文件
$db_files = glob("*.db");
$current_db = $_GET['db'] ?? ($db_files[0] ?? '');
// 错误处理
$message = '';
$db = null;
if ($current_db && in_array($current_db, $db_files)) {
try {
// 连接到SQLite数据库
$db = new PDO("sqlite:$current_db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
$message = "数据库连接失败: " . $e->getMessage();
}
} elseif ($current_db) {
$message = "数据库文件不存在: " . $current_db;
}
// 获取所有表名
$tables = array();
if ($db) {
try {
$result = $db->query("SELECT name FROM sqlite_master WHERE type='table'");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
$tables[] = $row['name'];
}
} catch(Exception $e) {
$message = "获取表失败: " . $e->getMessage();
}
}
// 处理表单提交
$executed_sql = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && $db) {
if (isset($_POST['execute_sql'])) {
// 执行SQL语句
$sql = $_POST['sql_statement'];
try {
if (stripos(trim($sql), 'select') === 0) {
// 查询语句
$result = $db->query($sql);
$data = array();
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
$message = "查询成功，返回 " . count($data) . " 行数据";
$executed_sql = $sql;
} else {
// 非查询语句
$affected_rows = $db->exec($sql);
$message = "执行成功，影响 " . $affected_rows . " 行";
$executed_sql = $sql;
}
} catch(PDOException $e) {
$message = "SQL执行错误: " . $e->getMessage();
}
} elseif (isset($_POST['truncate_table'])) {
// 清空表
$table = $_POST['table'];
// 构建SQL语句
$sql = "DELETE FROM $table";
$stmt = $db->prepare($sql);
if ($stmt->execute()) {
$message = "表 $table 已清空!";
$executed_sql = $sql;
// 重置自增ID (SQLite的特殊处理)
$reset_sql = "DELETE FROM sqlite_sequence WHERE name = ?";
$stmt_reset = $db->prepare($reset_sql);
$stmt_reset->execute([$table]);
} else {
$message = "清空表失败!";
}
} elseif (isset($_POST['delete_id'])) {
// 删除记录
$table = $_POST['table'];
$id = $_POST['delete_id'];
$id_column = $_POST['id_column'] ?? 'key'; // 使用key作为默认标识列
// 构建SQL语句
$sql = "DELETE FROM $table WHERE $id_column = ?";
$stmt = $db->prepare($sql);
if ($stmt->execute([$id])) {
$message = "记录删除成功!";
$executed_sql = $sql;
} else {
$message = "删除失败!";
}
} elseif (isset($_POST['save'])) {
// 更新或插入记录
$table = $_POST['table'];
$id = $_POST['id'] ?? null;
$id_column = $_POST['id_column'] ?? 'key'; // 使用key作为默认标识列
// 获取表结构以构建动态查询
$columns = array();
$result = $db->query("PRAGMA table_info($table)");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
if ($row['name'] != $id_column) {
$columns[] = $row['name'];
}
}
if ($id) {
// 更新记录
$set_parts = array();
$params = array();
foreach($columns as $col) {
$set_parts[] = "$col = ?";
$params[] = $_POST[$col] ?? '';
}
$params[] = $id;
$sql = "UPDATE $table SET " . implode(', ', $set_parts) . " WHERE $id_column = ?";
$stmt = $db->prepare($sql);
if ($stmt->execute($params)) {
$message = "记录更新成功!";
$executed_sql = $sql;
// 替换参数占位符为实际值
for($i = 0; $i < count($columns); $i++) {
$executed_sql = preg_replace('/\?/', "'" . ($_POST[$columns[$i]] ?? '') . "'", $executed_sql, 1);
}
} else {
$message = "更新失败!";
}
} else {
// 插入新记录
$placeholders = array_fill(0, count($columns), '?');
$params = array();
foreach($columns as $col) {
$params[] = $_POST[$col] ?? '';
}
$sql = "INSERT INTO $table (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
$stmt = $db->prepare($sql);
if ($stmt->execute($params)) {
$message = "新记录添加成功!";
$executed_sql = $sql;
// 替换参数占位符为实际值
for($i = 0; $i < count($columns); $i++) {
$executed_sql = preg_replace('/\?/', "'" . ($_POST[$columns[$i]] ?? '') . "'", $executed_sql, 1);
}
} else {
$message = "添加失败!";
}
}
}
}
// 获取当前选中的表
$current_table = $_GET['table'] ?? ($tables[0] ?? '');
// 初始化变量
$data = array();
$columns = array();
$edit_data = array();
// 获取主键列名
function getPrimaryKeyColumn($db, $table) {
try {
$result = $db->query("PRAGMA table_info($table)");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
if ($row['pk'] == 1) {
return $row['name'];
}
}
// 如果没有明确的主键，尝试使用常见的标识列
$common_keys = ['key', 'id', 'uuid', 'name'];
$result = $db->query("PRAGMA table_info($table)");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
if (in_array(strtolower($row['name']), $common_keys)) {
return $row['name'];
}
}
// 如果都没有，返回第一个列
$result = $db->query("PRAGMA table_info($table)");
$first_col = $result->fetch(PDO::FETCH_ASSOC);
return $first_col ? $first_col['name'] : 'key';
} catch(Exception $e) {
return 'key'; // 默认使用key
}
}
// 获取表数据和结构
if ($current_table && $db) {
// 获取主键列名
$primary_key_column = getPrimaryKeyColumn($db, $current_table);
// 获取表结构
try {
$result = $db->query("PRAGMA table_info($current_table)");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
$columns[] = $row;
}
} catch(Exception $e) {
$message = "获取表结构失败: " . $e->getMessage();
}
// 获取表数据（仅在不是编辑/添加操作时）
if (!isset($_GET['action']) || $_GET['action'] == 'sql') {
try {
$result = $db->query("SELECT * FROM $current_table");
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
} catch(Exception $e) {
$message = "获取表数据失败: " . $e->getMessage();
}
}
// 获取编辑数据
if (isset($_GET['action']) && ($_GET['action'] == 'edit' || $_GET['action'] == 'view') && isset($_GET['id'])) {
$edit_id = $_GET['id'];
try {
$stmt = $db->prepare("SELECT * FROM $current_table WHERE $primary_key_column = ?");
$stmt->execute([$edit_id]);
$edit_data = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$edit_data) {
$message = "找不到指定的记录!";
}
} catch(Exception $e) {
$message = "获取记录失败: " . $e->getMessage();
}
}
}
// 构建URL参数函数
function buildUrl($params = array()) {
$current_params = $_GET;
foreach ($params as $key => $value) {
$current_params[$key] = $value;
}
return '?' . http_build_query($current_params);
}
// 判断字段是否可能包含HTML内容
function isHtmlContent($value) {
if (!is_string($value)) return false;
// 如果值包含HTML标签，则认为是HTML内容
return preg_match('/<[a-z][\s\S]*>/i', $value) || strlen($value) > 200;
}
// 获取记录的唯一标识符
function getRecordId($row, $primary_key_column) {
return $row[$primary_key_column] ?? $row[array_keys($row)[0]] ?? '';
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>数据库管理</title>
<style>
* {
box-sizing: border-box;
}
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 20px;
line-height: 1.6;
background-color: #f5f5f5;
}
.container {
max-width: 1400px;
margin: 0 auto;
background-color: white;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0,0,0,0.1);
}
.database-selector {
margin-bottom: 20px;
padding: 15px;
background-color: #f8f9fa;
border-radius: 5px;
border: 1px solid #dee2e6;
}
.database-selector label {
font-weight: bold;
margin-right: 10px;
}
.database-selector select {
padding: 8px 12px;
border: 1px solid #ced4da;
border-radius: 4px;
background-color: white;
font-size: 16px;
min-width: 300px;
}
.message {
padding: 10px;
margin: 10px 0;
border-radius: 4px;
}
.success {
background-color: #d4edda;
color: #155724;
border: 1px solid #c3e6cb;
}
.error {
background-color: #f8d7da;
color: #721c24;
border: 1px solid #f5c6cb;
}
.tabs {
display: flex;
flex-wrap: wrap;
margin-bottom: 20px;
border-bottom: 1px solid #ddd;
}
.tab {
padding: 10px 15px;
background-color: #f1f1f1;
border: 1px solid #ddd;
border-bottom: none;
cursor: pointer;
margin-right: 5px;
border-radius: 4px 4px 0 0;
margin-bottom: -1px;
text-decoration: none;
color: #333;
}
.tab.active {
background-color: #fff;
font-weight: bold;
border-bottom: 1px solid white;
}
.table-container {
overflow-x: auto;
margin-bottom: 20px;
border: 1px solid #ddd;
border-radius: 4px;
}
table {
width: 100%;
border-collapse: collapse;
min-width: 800px;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
text-align: left;
white-space: nowrap;
overflow: hidden;
text-overflow: ellipsis;
max-width: 300px;
}
th {
background-color: #f2f2f2;
position: sticky;
top: 0;
}
tr:nth-child(even) {
background-color: #f9f9f9;
}
.actions {
white-space: nowrap;
}
.actions a {
margin-right: 10px;
text-decoration: none;
padding: 3px 8px;
border-radius: 3px;
display: inline-block;
}
.edit {
background-color: #4CAF50;
color: white;
}
.delete {
background-color: #f44336;
color: white;
border: none;
padding: 4px 8px;
border-radius: 3px;
cursor: pointer;
}
.add-new {
display: inline-block;
margin-bottom: 15px;
padding: 8px 15px;
background-color: #007bff;
color: white;
text-decoration: none;
border-radius: 4px;
margin-right: 10px;
}
.truncate-table {
display: inline-block;
margin-bottom: 15px;
padding: 8px 15px;
background-color: #ffc107;
color: #212529;
text-decoration: none;
border-radius: 4px;
border: none;
cursor: pointer;
}
.form-container {
background-color: #f9f9f9;
padding: 20px;
border-radius: 5px;
margin-top: 20px;
}
.form-group {
margin-bottom: 15px;
}
label {
display: block;
margin-bottom: 5px;
font-weight: bold;
}
input[type="text"],
input[type="number"],
textarea {
width: 100%;
padding: 8px;
border: 1px solid #ddd;
border-radius: 4px;
}
button {
background-color: #4CAF50;
color: white;
padding: 10px 15px;
border: none;
border-radius: 4px;
cursor: pointer;
}
button:hover {
background-color: #45a049;
}
.cancel {
background-color: #6c757d;
margin-left: 10px;
text-decoration: none;
padding: 10px 15px;
border-radius: 4px;
color: white;
display: inline-block;
}
.cancel:hover {
background-color: #5a6268;
}
.sql-container {
background-color: #f0f8ff;
padding: 20px;
border-radius: 5px;
margin-top: 20px;
}
.sql-output {
background-color: #f5f5f5;
padding: 10px;
border-radius: 4px;
margin-top: 10px;
font-family: monospace;
white-space: pre-wrap;
border: 1px solid #ddd;
}
.table-actions {
margin-bottom: 15px;
}
/* 内容显示框样式 */
.content-box {
max-height: 80px;
overflow: auto;
border: 1px solid #ddd;
padding: 8px;
background-color: #f9f9f9;
border-radius: 4px;
font-family: monospace;
font-size: 12px;
white-space: pre-wrap;
word-break: break-all;
resize: vertical;
min-height: 40px;
}
.expand-button {
background: none;
border: none;
color: #007bff;
cursor: pointer;
font-size: 12px;
padding: 2px 5px;
margin-top: 5px;
}
.expanded {
max-height: none;
}
/* 记录详情模态框 */
.modal {
display: none;
position: fixed;
top: 0;
left: 0;
width: 100%;
height: 100%;
background-color: rgba(0,0,0,0.5);
z-index: 1000;
overflow: auto;
}
.modal-content {
background-color: white;
margin: 5% auto;
padding: 20px;
border-radius: 8px;
width: 90%;
max-width: 1000px;
max-height: 80vh;
overflow: auto;
}
.close {
float: right;
font-size: 24px;
font-weight: bold;
cursor: pointer;
}
.record-details {
width: 100%;
border-collapse: collapse;
}
.record-details th {
width: 200px;
text-align: right;
padding-right: 15px;
background-color: #f9f9f9;
}
.record-details td {
padding: 8px;
border-bottom: 1px solid #eee;
}
.view-details {
background-color: #17a2b8;
color: white;
padding: 3px 8px;
border-radius: 3px;
text-decoration: none;
display: inline-block;
margin-right: 5px;
}
/* 响应式设计 */
@media (max-width: 768px) {
.container {
padding: 10px;
}
.database-selector select {
min-width: 100%;
}
.tabs {
flex-direction: column;
}
.tab {
margin-bottom: 5px;
border-radius: 4px;
}
.table-container {
font-size: 14px;
}
th, td {
padding: 6px;
max-width: 150px;
}
}
</style>
</head>
<body>
<div class="container">
<h1>数据库管理</h1>
<div class="database-selector">
<label for="db-select">选择数据库:</label>
<select id="db-select" onchange="changeDatabase(this.value)">
<?php foreach($db_files as $db_file): ?>
<option value="<?php echo $db_file; ?>" <?php echo $current_db == $db_file ? 'selected' : ''; ?>>
<?php echo $db_file; ?>
</option>
<?php endforeach; ?>
</select>
</div>
<?php if ($message): ?>
<div class="message <?php echo strpos($message, '成功') !== false || strpos($message, '查询成功') !== false ? 'success' : 'error'; ?>">
<?php echo $message; ?>
</div>
<?php endif; ?>
<?php if ($executed_sql): ?>
<div class="sql-output">
<strong>执行的SQL语句:</strong><br>
<?php echo htmlspecialchars($executed_sql); ?>
</div>
<?php endif; ?>
<?php if ($db): ?>
<div class="tabs">
<?php foreach($tables as $table): ?>
<a href="<?php echo buildUrl(['table' => $table]); ?>" class="tab <?php echo $current_table == $table ? 'active' : ''; ?>">
<?php echo $table; ?>
</a>
<?php endforeach; ?>
<a href="<?php echo buildUrl(['action' => 'sql']); ?>" class="tab <?php echo isset($_GET['action']) && $_GET['action'] == 'sql' ? 'active' : ''; ?>">
执行SQL
</a>
</div>
<?php if (isset($_GET['action']) && $_GET['action'] == 'sql'): ?>
<div class="sql-container">
<h2>执行SQL语句</h2>
<form method="post">
<div class="form-group">
<label for="sql_statement">SQL语句:</label>
<textarea id="sql_statement" name="sql_statement" rows="5" placeholder="输入SQL语句，例如: SELECT * FROM users"><?php echo isset($_POST['sql_statement']) ? htmlspecialchars($_POST['sql_statement']) : ''; ?></textarea>
</div>
<button type="submit" name="execute_sql">执行SQL</button>
</form>
<?php if (isset($_POST['execute_sql']) && isset($data) && is_array($data)): ?>
<?php if (count($data) > 0): ?>
<h3>查询结果 (<?php echo count($data); ?> 行)</h3>
<div class="table-container">
<table>
<thead>
<tr>
<?php foreach(array_keys($data[0]) as $col): ?>
<th><?php echo $col; ?></th>
<?php endforeach; ?>
</tr>
</thead>
<tbody>
<?php foreach($data as $row): ?>
<tr>
<?php foreach($row as $value): ?>
<td>
<?php if (isHtmlContent($value)): ?>
<div class="content-box" id="content-<?php echo md5($value); ?>">
<?php echo htmlspecialchars($value); ?>
</div>
<button class="expand-button" onclick="toggleExpand('content-<?php echo md5($value); ?>')">展开/收起</button>
<?php else: ?>
<?php echo $value; ?>
<?php endif; ?>
</td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php else: ?>
<p>查询成功，但没有返回数据。</p>
<?php endif; ?>
<?php endif; ?>
</div>
<?php elseif ($current_table): ?>
<?php
// 获取当前表的主键列名
$primary_key_column = $current_table ? getPrimaryKeyColumn($db, $current_table) : 'key';
?>
<?php if (isset($_GET['action']) && ($_GET['action'] == 'add' || $_GET['action'] == 'edit')): ?>
<div class="form-container">
<h2><?php echo isset($_GET['id']) ? '编辑记录' : '添加新记录'; ?></h2>
<form method="post">
<input type="hidden" name="table" value="<?php echo $current_table; ?>">
<input type="hidden" name="id_column" value="<?php echo $primary_key_column; ?>">
<?php if (isset($_GET['id'])): ?>
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>">
<?php endif; ?>
<?php if (is_array($columns) && count($columns) > 0): ?>
<?php foreach($columns as $col): ?>
<?php if ($col['name'] != $primary_key_column): ?>
<div class="form-group">
<label for="<?php echo $col['name']; ?>"><?php echo $col['name']; ?>:</label>
<?php
// 检查字段值是否可能包含HTML内容
$field_value = isset($edit_data[$col['name']]) ? $edit_data[$col['name']] : '';
if (isHtmlContent($field_value)):
?>
<textarea id="<?php echo $col['name']; ?>" name="<?php echo $col['name']; ?>" rows="8"><?php echo htmlspecialchars($field_value); ?></textarea>
<?php else: ?>
<input type="text" id="<?php echo $col['name']; ?>" name="<?php echo $col['name']; ?>" value="<?php echo $field_value; ?>">
<?php endif; ?>
</div>
<?php endif; ?>
<?php endforeach; ?>
<?php else: ?>
<p>无法获取表结构。</p>
<?php endif; ?>
<button type="submit" name="save">保存</button>
<a href="<?php echo buildUrl(['table' => $current_table]); ?>" class="cancel">取消</a>
</form>
</div>
<?php elseif (isset($_GET['action']) && $_GET['action'] == 'view' && isset($_GET['id'])): ?>
<div class="form-container">
<h2>记录详情</h2>
<?php if (is_array($edit_data) && count($edit_data) > 0): ?>
<table class="record-details">
<tbody>
<?php foreach($edit_data as $key => $value): ?>
<tr>
<th><?php echo $key; ?>:</th>
<td>
<?php if (isHtmlContent($value)): ?>
<div class="content-box">
<?php echo htmlspecialchars($value); ?>
</div>
<?php else: ?>
<?php echo $value; ?>
<?php endif; ?>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php else: ?>
<p>找不到指定的记录。</p>
<?php endif; ?>
<a href="<?php echo buildUrl(['table' => $current_table]); ?>" class="cancel">返回</a>
</div>
<?php else: ?>
<div class="table-actions">
<a href="<?php echo buildUrl(['table' => $current_table, 'action' => 'add']); ?>" class="add-new">添加新记录</a>
<?php if (is_array($data) && count($data) > 0): ?>
<form method="post" style="display:inline;">
<input type="hidden" name="table" value="<?php echo $current_table; ?>">
<button type="submit" class="truncate-table" onclick="return confirm('警告：这将永久删除表中所有数据！\n\n您确定要清空表 <?php echo $current_table; ?> 吗？')">清空表</button>
<input type="hidden" name="truncate_table" value="1">
</form>
<?php endif; ?>
</div>
<?php if (is_array($data) && count($data) > 0): ?>
<div class="table-container">
<table>
<thead>
<tr>
<?php
// 只显示前8个字段，避免表格过于拥挤
$display_columns = array_slice($columns, 0, 8);
foreach($display_columns as $col): ?>
<th><?php echo $col['name']; ?></th>
<?php endforeach; ?>
<th>操作</th>
</tr>
</thead>
<tbody>
<?php foreach($data as $row): ?>
<?php
// 获取记录的唯一标识符
$record_id = getRecordId($row, $primary_key_column);
?>
<tr>
<?php foreach($display_columns as $col): ?>
<td title="<?php echo htmlspecialchars($row[$col['name']]); ?>">
<?php
$value = $row[$col['name']];
// 如果字段值可能包含HTML内容，使用内容框显示
if (isHtmlContent($value)):
?>
<div class="content-box" id="content-<?php echo $record_id . '-' . $col['name']; ?>">
<?php echo htmlspecialchars(substr($value, 0, 100)) . (strlen($value) > 100 ? '...' : ''); ?>
</div>
<?php else: ?>
<?php echo strlen($value) > 50 ? substr($value, 0, 50) . '...' : $value; ?>
<?php endif; ?>
</td>
<?php endforeach; ?>
<td class="actions">
<a href="<?php echo buildUrl(['table' => $current_table, 'action' => 'view', 'id' => $record_id]); ?>" class="view-details">详情</a>
<a href="<?php echo buildUrl(['table' => $current_table, 'action' => 'edit', 'id' => $record_id]); ?>" class="edit">编辑</a>
<form method="post" style="display:inline;">
<input type="hidden" name="table" value="<?php echo $current_table; ?>">
<input type="hidden" name="id_column" value="<?php echo $primary_key_column; ?>">
<input type="hidden" name="delete_id" value="<?php echo $record_id; ?>">
<button type="submit" class="delete" onclick="return confirm('确定要删除这条记录吗？')">删除</button>
</form>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php else: ?>
<p>表中没有数据。</p>
<?php endif; ?>
<?php endif; ?>
<?php else: ?>
<p>数据库中没有表。</p>
<?php endif; ?>
<?php else: ?>
<p>请选择一个数据库文件。</p>
<?php endif; ?>
</div>
<script>
// 切换数据库
function changeDatabase(dbFile) {
window.location.href = '?db=' + encodeURIComponent(dbFile);
}
// 展开/收起内容框
function toggleExpand(contentId) {
var contentBox = document.getElementById(contentId);
contentBox.classList.toggle('expanded');
}
</script>
</body>
</html>© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
![表情[aini]-红穆笔记](https://www.4s5.cn/wp-content/themes/zibll/img/smilies/aini.gif)
![表情[ciya]-红穆笔记](https://www.4s5.cn/wp-content/themes/zibll/img/smilies/ciya.gif)
![表情[xia]-红穆笔记](https://www.4s5.cn/wp-content/themes/zibll/img/smilies/xia.gif)
暂无评论内容