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

PHP 檔案交換系統（一）：首頁列表與資料庫設計

編輯：PHP 綜合知識

專案背景

內部局域網檔案交換平台，功能需求：

檔案上傳 / 下載
分類管理（通知、下發、上報）
顯示上傳者、日期、說明
簡單權控（登入者可上傳）

本文對應 index.php，重構為現代 MVC 風格，採用 PDO、模板分離、響應式表格。

資料庫設計

CREATE TABLE `upfile` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `filename` VARCHAR(255) NOT NULL COMMENT '實體儲存檔名（隨機）',
    `original_name` VARCHAR(255) NOT NULL COMMENT '原始檔名',
    `fileshow` VARCHAR(500) DEFAULT '' COMMENT '檔案說明',
    `date` DATE NOT NULL COMMENT '上傳日期',
    `uploader` VARCHAR(100) NOT NULL COMMENT '上傳者',
    `type` ENUM('通知','下發文件','上報文件','其他') NOT NULL DEFAULT '其他',
    `size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `mime` VARCHAR(100) NOT NULL,
    `downloads` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_date_type` (`date`, `type`),
    INDEX `idx_uploader` (`uploader`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

首頁控制器（index.php）

<?php
// index.php
declare(strict_types=1);
require_once __DIR__ . '/bootstrap.php'; // 自動載入 $pdo、$twig、Session、CSRF

// 1. 取得篩選參數
$type = $_GET['type'] ?? '';
$keyword = $_GET['q'] ?? '';
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;

// 2. 建構查詢
$where = ['1=1'];
$params = [];
if ($type !== '') {
    $where[] = 'type = ?';
    $params[] = $type;
}
if ($keyword !== '') {
    $where[] = '(original_name LIKE ? OR fileshow LIKE ?)';
    $kw = "%$keyword%";
    $params[] = $kw;
    $params[] = $kw;
}
$whereSql = implode(' AND ', $where);

// 3. 總筆數
$total = (int)$pdo->query("SELECT COUNT(*) FROM upfile WHERE $whereSql")->fetchColumn();
$totalPages = max(1, (int)ceil($total / $perPage));
$page = min($page, $totalPages);
$offset = ($page - 1) * $perPage;

// 4. 取得資料
$stmt = $pdo->prepare("SELECT id, filename, original_name, fileshow, date, uploader, type, size, downloads
                       FROM upfile WHERE $whereSql ORDER BY date DESC, id DESC LIMIT ? OFFSET ?");
$params[] = $perPage;
$params[] = $offset;
$stmt->execute($params);
$files = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 5. 傳遞給模板
echo $twig->render('index.twig', [
    'files' => $files,
    'pagination' => [
        'page' => $page,
        'totalPages' => $totalPages,
        'total' => $total,
        'type' => $type,
        'q' => $keyword
    ],
    'types' => ['通知','下發文件','上報文件','其他'],
    'flash' => $_SESSION['flash'] ?? null
]);
unset($_SESSION['flash']);
?>

Twig 模板（index.twig）

{# templates/index.twig #}
<!DOCTYPE html>
<html lang="zh-Hant">
<head>
    <meta charset="utf-8">
    <title>檔案交換區</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-4">
    <div class="d-flex justify-content-between align-items-center mb-3">
        <h1 class="h3 mb-0">檔案交換區</h1>
        <a href="upload.php" class="btn btn-primary">上傳檔案</a>
    </div>

    {# 篩選表單 #}
    <form class="row g-2 mb-3" method="get">
        <div class="col-md-3">
            <select name="type" class="form-select">
                <option value="">全部分類</option>
                {% for t in types %}
                    <option value="{{ t }}" {{ 'selected' if t == type }}>{{ t }}</option>
                {% endfor %}
            </select>
        </div>
        <div class="col-md-6">
            <input type="search" name="q" class="form-control" placeholder="搜尋檔名或說明..." value="{{ q|e }}">>
        </div>
        <div class="col-md-3">
            <button class="btn btn-outline-secondary w-100" type="submit">篩選</button>
        </div>
    </form>

    {# 訊息提示 #}
    {% if flash %}
        <div class="alert alert-info alert-dismissible fade show" role="alert">
            {{ flash }}
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    {% endif %}

    {# 檔案列表 #}
    {% if files|length > 0 %}
        <div class="table-responsive">
            <table class="table table-striped table-hover align-middle">
                <thead class="table-light">
                    <tr>
                        <th>檔名</th>
                        <th>上傳日期</th>
                        <th>上傳者</th>
                        <th>說明</th>
                        <th>類型</th>
                        <th>大小</th>
                        <th>下載次數</th>
                    </tr>
                </thead>
                <tbody>
                {% for f in files %}
                    <tr>
                        <td>
                            <a href="download.php?id={{ f.id }}" class="text-decoration-none">
                                {{ f.original_name|e }}
                            </a>
                        </td>
                        <td>{{ f.date|date('Y-m-d') }}</td>
                        <td>{{ f.uploader|e }}</td>
                        <td>{{ f.fileshow|e|default('—') }}</td>
                        <td><span class="badge bg-secondary">{{ f.type }}</span></td>
                        <td>{{ f.size|format_bytes }}</td>
                        <td>{{ f.downloads }}</td>
                    </tr>
                {% endfor %}
                </tbody>
            </table>
        </div>

        {# 分頁 #}
        <nav aria-label="Page navigation">
            <ul class="pagination justify-content-center">
                {% if page > 1 %}
                    <li class="page-item">
                        <a class="page-link" href="?page={{ page-1 }}&type={{ type|url_encode }}&q={{ q|url_encode }}">上一頁</a>
                    </li>
                {% endif %}
                {% for p in 1..totalPages %}
                    <li class="page-item {{ 'active' if p == page }}">
                        <a class="page-link" href="?page={{ p }}&type={{ type|url_encode }}&q={{ q|url_encode }}">{{ p }}</a>
                    </li>
                {% endfor %}
                {% if page < totalPages %}
                    <li class="page-item">
                        <a class="page-link" href="?page={{ page+1 }}&type={{ type|url_encode }}&q={{ q|url_encode }}">下一頁</a>
                    </li>
                {% endif %}
            </ul>
        </nav>
    {% else %}
        <div class="alert alert-warning text-center">目前沒有檔案</div>
    {% endif %}
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

自訂 Twig 過濾器：format_bytes

$twig->addFilter(new Twig\TwigFilter('format_bytes', function (int $bytes): string {
    $units = ['B','KB','MB','GB','TB'];
    $i = 0;
    while ($bytes >= 1024 && $i < count($units)-1) {
        $bytes /= 1024;
        $i++;
    }
    return round($bytes, 2) . ' ' . $units[$i];
}));

遷移重點總結

原始做法	重構方案
HTML/PHP 混寫、`printf` 拼接	Twig 模板引擎，邏輯與視圖分離
`mysql_*` + 字串拼接 SQL	PDO + 預處理 + 參數綁定
無分頁、全表撈取	LIMIT/OFFSET 分頁、索引優化
硬編碼樣式、Table 佈局	Bootstrap 5 響應式、語意化 HTML
無搜尋/篩選	支援關鍵字、類型雙重篩選
無 CSRF、XSS 防護	Twig 自動跳脫 + CSRF Token