您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP mysqli 將資料表數據渲染為 HTML 表格：含表頭自動生成、分頁與樣式建議

編輯：PHP 綜合知識

需求場景

後台管理系統、數據導出預覽、調試工具中，常需快速將資料表數據以表格形式展示。核心需求：自動獲取欄位名作為表頭、遍歷數據行、處理空值、大數據分頁、基本樣式美化。

基礎實現：動態表頭 + 數據行

<?php
$mysqli = new MySQLi("localhost", "root", "123456", "liuyan");
if ($mysqli->connect_error) {
    die("連線失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4");

function renderTable($mysqli, $tableName, $limit = 50, $offset = 0) {
    // 防注入：表名白名單校驗
    $allowedTables = ['news', 'user', 'account'];
    if (!in_array($tableName, $allowedTables)) {
        return "非法表名";
    }

    $sql = "SELECT * FROM `$tableName` LIMIT $offset, $limit";
    $res = $mysqli->query($sql);
    if (!$res) {
        return "查詢失敗: " . $mysqli->error . "";
    }

    $html = "";
    
    // 1. 生成表頭
    $html .= "";
    while ($field = $res->fetch_field()) {
        $html .= "";
    }
    $html .= "";

    // 2. 生成數據行
    $html .= "";
    while ($row = $res->fetch_assoc()) {
        $html .= "";
        foreach ($row as $value) {
            $display = $value === null ? "NULL" : htmlspecialchars($value);
            $html .= "";
        }
        $html .= "";
    }
    $html .= "";
    $html .= "" . htmlspecialchars($field->name) . "
$display";

    // 3. 統計信息
    $html .= "共 {$res->num_rows} 行 × {$res->field_count} 列";

    $res->free();
    return $html;
}

// 使用
echo renderTable($mysqli, 'news', 20);
$mysqli->close();
?>

" . htmlspecialchars($field->name) . "
$display

進階版：含分頁、排序、搜索、響應式樣式

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>資料表瀏覽器</title>
    <style>
        .data-table { width: 100%; border-collapse: collapse; font-size: 14px; }
        .data-table th, .data-table td { border: 1px solid #ddd; padding: 8px 12px; text-align: left; }
        .data-table th { background: #f5f5f5; font-weight: 600; position: sticky; top: 0; }
        .data-table tr:nth-child(even) { background: #fafafa; }
        .data-table tr:hover { background: #eef; }
        .text-muted { color: #999; font-style: italic; }
        .pagination { margin: 20px 0; }
        .pagination a, .pagination span { display: inline-block; padding: 6px 12px; margin: 0 2px; border: 1px solid #ddd; text-decoration: none; color: #333; }
        .pagination .current { background: #007bff; color: #fff; border-color: #007bff; }
        .toolbar { margin-bottom: 15px; display: flex; gap: 10px; flex-wrap: wrap; align-items: center; }
        .toolbar select, .toolbar input { padding: 6px 10px; border: 1px solid #ccc; border-radius: 4px; }
        .table-wrapper { overflow-x: auto; max-height: 70vh; overflow-y: auto; }
    </style>
</head>
<body>
<div class="container">
    <h2>資料表：<span id="tableName"></span></h2>
    
    <div class="toolbar">
        <select id="tableSelect" onchange="location.href='?table='+this.value">
            <option value="news">news</option>
            <option value="user">user</option>
            <option value="account">account</option>
        </select>
        <input type="text" id="search" placeholder="搜索..." onkeydown="if(event.key==='Enter')search()">
        <button onclick="search()">搜索</button>
        <select id="pageSize" onchange="goPage(1)">
            <option value="10">10/頁</option>
            <option value="25" selected>25/頁</option>
            <option value="50">50/頁</option>
            <option value="100">100/頁</option>
        </select>
    </div>

    <div class="table-wrapper" id="tableContainer"></div>
    <div class="pagination" id="pagination"></div>
</div>

<script>
let currentTable = '';
let currentPage = 1;
let pageSize = 25;
let searchKeyword = '';

document.getElementById('tableSelect').value = currentTable;
document.getElementById('tableName').textContent = currentTable;

async function loadTable() {
    const url = `api.php?table=${currentTable}&page=${currentPage}&size=${pageSize}&search=${encodeURIComponent(searchKeyword)}`;
    const res = await fetch(url);
    const data = await res.json();
    renderTable(data);
    renderPagination(data.total, data.page, data.pageSize);
}

function renderTable(data) {
    if (data.error) {
        document.getElementById('tableContainer').innerHTML = `${data.error}`;
        return;
    }
    let html = '';
    data.columns.forEach(col => html += ``);
    html += '';
    data.rows.forEach(row => {
        html += '';
        row.forEach(val => html += ``);
        html += '';
    });
    html += '${escapeHtml(col)}
${val === null ? 'NULL' : escapeHtml(val)}';
    document.getElementById('tableContainer').innerHTML = html;
}

function renderPagination(total, page, size) {
    const totalPages = Math.ceil(total / size);
    let html = '';
    for (let i = 1; i <= totalPages; i++) {
        if (i === 1 || i === totalPages || (i >= page - 2 && i <= page + 2)) {
            html += `${i}`;
        } else if (i === page - 3 || i === page + 3) {
            html += '...';
        }
    }
    document.getElementById('pagination').innerHTML = html;
}

function goPage(page) { currentPage = page; loadTable(); }
function search() { searchKeyword = document.getElementById('search').value; currentPage = 1; loadTable(); }
function escapeHtml(text) { return String(text).replace(/[&<>"']/g, m => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[m])); }

loadTable();
</script>
</body>
</html>

${escapeHtml(col)}
${val === null ? 'NULL' : escapeHtml(val)}

後端 API (api.php)

<?php
header('Content-Type: application/json; charset=utf-8');
$mysqli = new MySQLi("localhost", "root", "123456", "liuyan");
$mysqli->set_charset("utf8mb4");

$table = $_GET['table'] ?? 'news';
$page = max(1, intval($_GET['page'] ?? 1));
$size = max(1, min(200, intval($_GET['size'] ?? 25)));
$search = $_GET['search'] ?? '';
$offset = ($page - 1) * $size;

$allowed = ['news', 'user', 'account'];
if (!in_array($table, $allowed)) {
    echo json_encode(['error' => '非法表名']);
    exit;
}

$where = '';
$params = [];
$types = '';
if ($search) {
    // 簡單搜索：所有文本欄位 LIKE
    $cols = $mysqli->query("SHOW COLUMNS FROM `$table`")->fetch_all(MYSQLI_ASSOC);
    $textCols = array_filter($cols, fn($c) => in_array($c['Type'], ['varchar', 'text', 'char', 'longtext']));
    if ($textCols) {
        $conditions = [];
        foreach ($textCols as $col) {
            $conditions[] = "`{$col['Field']}` LIKE ?";
            $params[] = "%$search%";
            $types .= 's';
        }
        $where = 'WHERE ' . implode(' OR ', $conditions);
    }
}

// 總數
$countSql = "SELECT COUNT(*) FROM `$table` $where";
$stmt = $mysqli->prepare($countSql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$total = $stmt->get_result()->fetch_row()[0];
$stmt->close();

// 數據
$dataSql = "SELECT * FROM `$table` $where LIMIT ?, ?";
$stmt = $mysqli->prepare($dataSql);
$allParams = array_merge($params, [$offset, $size]);
$allTypes = $types . 'ii';
$stmt->bind_param($allTypes, ...$allParams);
$stmt->execute();
$res = $stmt->get_result();

$columns = [];
while ($field = $res->fetch_field()) $columns[] = $field->name;
$rows = $res->fetch_all(MYSQLI_NUM);
$res->free();
$stmt->close();

echo json_encode([
    'columns' => $columns,
    'rows'    => $rows,
    'total'   => $total,
    'page'    => $page,
    'pageSize'=> $size,
]);
$mysqli->close();
?>

關鍵技術點總結

功能	實現要點
動態表頭	`fetch_field()` 獲取欄位元數據
NULL 值顯示	三元運算符區分 null 與空字符串
XSS 防護	`htmlspecialchars()` 輸出轉義
表名安全	白名單校驗，防止 SQL 注入
大數據分頁	LIMIT offset, size + COUNT(*) 總數
搜索功能	動態構建 WHERE 條件，預處理綁定參數
響應式表格	CSS `overflow-x: auto`、`th position: sticky`
前後端分離	API 返回 JSON，前端渲染，體驗更流暢

總結

從簡單的fetch_field() + fetch_assoc()生成靜態表格，到前後端分離的可搜索、可分頁、響應式數據表格，核心思路一致：元數據驅動表頭、預處理保證安全、分頁控制性能、前端增強體驗。實際專案中建議封裝為通用組件（如 Vue/React Table + 通用 API），複用於所有後台管理頁面。