查詢資料表結構與資料是後台管理、API 開發常見需求。舊教學常用 mysql_* 函式(PHP 7.0 移除),本文提供 **PDO 現代化完整範例**,涵蓋取得欄位中文名稱、資料型別、預設值、註解,以及分頁查詢資料、HTML 表格輸出、XSS 防護等實戰細節。
<?php
// config/database.php
return [
'dsn' => 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4',
'username' => 'app_user',
'password' => 'secret',
'options' => [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
],
];
// bootstrap.php
$config = require __DIR__ . '/config/database.php';
$pdo = new PDO($config['dsn'], $config['username'], $config['password'], $config['options']);
?>
<?php
function getTableColumns(PDO $pdo, string $table, string $schema = null): array
{
$sql = "SELECT
COLUMN_NAME AS `name`,
COLUMN_TYPE AS `type`,
IS_NULLABLE AS `nullable`,
COLUMN_DEFAULT AS `default`,
COLUMN_KEY AS `key`,
EXTRA AS `extra`,
COLUMN_COMMENT AS `comment`,
CHARACTER_SET_NAME AS `charset`,
COLLATION_NAME AS `collation`
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION";
$db = $schema ?? $pdo->query("SELECT DATABASE()")->fetchColumn();
$stmt = $pdo->prepare($sql);
$stmt->execute([$db, $table]);
return $stmt->fetchAll();
}
// 使用
$columns = getTableColumns($pdo, 'users');
echo "";
echo "欄位 型別 Null 預設 Key Extra 註解 ";
foreach ($columns as $col) {
echo "";
echo "", htmlspecialchars($col['name']), " ";
echo "", htmlspecialchars($col['type']), " ";
echo "", $col['nullable'] === 'YES' ? 'YES' : 'NO', " ";
echo "", htmlspecialchars($col['default'] ?? 'NULL'), " ";
echo "", htmlspecialchars($col['key']), " ";
echo "", htmlspecialchars($col['extra']), " ";
echo "", htmlspecialchars($col['comment']), " ";
echo " ";
}
echo "
";
?>
<?php
function renderTable(PDO $pdo, string $table, array $columns, int $page = 1, int $perPage = 20): void
{
$offset = ($page - 1) * $perPage;
// 總筆數
$total = $pdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
$totalPages = (int)ceil($total / $perPage);
// 查詢資料(白名單欄位防注入)
$cols = implode(', ', array_map(fn($c) => "`$c`", $columns));
$stmt = $pdo->prepare("SELECT $cols FROM `$table` LIMIT ? OFFSET ?");
$stmt->bindValue(1, $perPage, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();
// 輸出
echo "\n";
echo " ";
foreach ($columns as $col) echo "", htmlspecialchars($col), " ";
echo " \n \n";
foreach ($rows as $row) {
echo " ";
foreach ($columns as $col) {
$val = $row[$col] ?? '';
// 根據型別格式化
if (is_bool($val)) $val = $val ? '是' : '否';
elseif (is_null($val)) $val = 'NULL';
elseif ($val instanceof DateTime) $val = $val->format('Y-m-d H:i:s');
echo "", htmlspecialchars((string)$val), " ";
}
echo " \n";
}
echo "
\n";
// 分頁導航
if ($totalPages > 1) {
echo "";
}
}
// 使用:只顯示指定欄位(安全)
$displayCols = ['id', 'username', 'email', 'created_at', 'status'];
renderTable($pdo, 'users', $displayCols, (int)($_GET['page'] ?? 1), 25);
?>
<?php
$columns = array_column(getTableColumns($pdo, 'orders'), 'name');
renderTable($pdo, 'orders', $columns);
?>
implode(', ', array_map(fn($c) => "`$c`", $columns)) 確保只查詢允許的欄位,防止 SQL 注入。LIMIT ? OFFSET ? 綁定整數參數。SELECT *,只選需要欄位;考慮游標(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false)逐行處理。OFFSET 深分頁效能劣化(可改用游標式分頁)。DB::table('users')->paginate(20)$conn->fetchAllAssociative('SELECT * FROM users LIMIT 20 OFFSET 0')拋棄 mysql_*,擁抱 PDO,不僅相容 PHP 8.2+,更能寫出安全、可維護、效能優異的資料庫操作代碼。