動態取得資料表欄位名稱是建構通用 CRUD、匯出工具、動態表單的基礎。本文展示使用 PDO(現代標準)與 mysqli 兩種方式,並說明避坑指南。
PDO 提供統一介面、支援預處理語句、例外處理,是現代 PHP 專案首選。
<?php
function getTableColumnsPDO(PDO $pdo, string $table, string $database = null): array {
$sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION";
$db = $database ?? $pdo->query("SELECT DATABASE()")->fetchColumn();
$stmt = $pdo->prepare($sql);
$stmt->execute([$db, $table]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// 使用範例
$pdo = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8mb4", 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$columns = getTableColumnsPDO($pdo, 'users');
foreach ($columns as $col) {
echo "{$col['COLUMN_NAME']} ({$col['DATA_TYPE']}) - {$col['COLUMN_COMMENT']}\n";
}
<?php
function getTableColumnsMySQLi(mysqli $mysqli, string $table, string $database = null): array {
$db = $database ?? $mysqli->query("SELECT DATABASE()")->fetch_row()[0];
$sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ss', $db, $table);
$stmt->execute();
$result = $stmt->get_result();
return $result->fetch_all(MYSQLI_ASSOC);
}
// 使用範例
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$mysqli->set_charset('utf8mb4');
$columns = getTableColumnsMySQLi($mysqli, 'users');
print_r($columns);
<?php
// 僅需欄位名稱陣列時
function getColumnNamesSimple(PDO $pdo, string $table): array {
$stmt = $pdo->query("SHOW COLUMNS FROM `$table`");
return $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // 只取 Field 欄位
}
// 或 DESCRIBE
$stmt = $pdo->query("DESCRIBE `$table`");
$names = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
<?php
function buildInsertSQL(PDO $pdo, string $table, array $data): array {
$columns = getTableColumnsPDO($pdo, $table);
$allowed = array_column($columns, 'COLUMN_NAME');
// 過濾資料:只保留資料表存在的欄位,排除自增主鍵
$filtered = array_intersect_key($data, array_flip($allowed));
foreach ($columns as $col) {
if ($col['COLUMN_KEY'] === 'PRI' && strpos($col['COLUMN_DEFAULT'], 'auto_increment') !== false) {
unset($filtered[$col['COLUMN_NAME']]);
}
}
$fields = implode(', ', array_map(fn($c) => "`$c`", array_keys($filtered)));
$placeholders = implode(', ', array_map(fn($c) => ":$c", array_keys($filtered)));
$sql = "INSERT INTO `$table` ($fields) VALUES ($placeholders)";
return [$sql, $filtered];
}
// 使用
[$sql, $params] = buildInsertSQL($pdo, 'users', $_POST);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);