PHP MySQL 連線與資料新增完整實作：PDO 最佳實踐、預處理語句與錯誤處理

資料庫連線：PDO 單例模式

<?php
// config/database.php
return [
    'dsn'      => 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4',
    'username' => 'app_user',
    'password' => 'strong_password',
    'options'  => [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false, // 真實預處理
    ],
];

// Database.php
class Database {
    private static ?PDO $instance = null;
    private function __construct() {}
    public static function getPDO(): PDO {
        if (self::$instance === null) {
            $config = require __DIR__ . '/config/database.php';
            self::$instance = new PDO(
                $config['dsn'],
                $config['username'],
                $config['password'],
                $config['options']
            );
        }
        return self::$instance;
    }
}
?>

建立資料表 Migration

<?php
// migrations/create_links_table.php
$pdo = Database::getPDO();
$pdo->exec("
    CREATE TABLE IF NOT EXISTS links (
        id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name        VARCHAR(100) NOT NULL,
        url         VARCHAR(500) NOT NULL,
        description VARCHAR(255) DEFAULT '',
        contact     VARCHAR(100) DEFAULT '',
        sort_order  INT UNSIGNED DEFAULT 0,
        category_id BIGINT UNSIGNED NULL,
        is_visible  TINYINT(1) DEFAULT 1,
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_category (category_id),
        INDEX idx_visible (is_visible)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");
echo "Table created.\n";
?>

新增資料：預處理語句防注入

<?php
// src/LinkRepository.php
class LinkRepository {
    private PDO $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }

    public function create(array $data): int {
        $sql = "INSERT INTO links (name, url, description, contact, sort_order, category_id, is_visible)
                VALUES (:name, :url, :desc, :contact, :sort, :cat, :vis)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':name'    => $data['name'] ?? '',
            ':url'     => $data['url'] ?? '',
            ':desc'    => $data['description'] ?? '',
            ':contact' => $data['contact'] ?? '',
            ':sort'    => $data['sort_order'] ?? 0,
            ':cat'     => $data['category_id'] ?? null,
            ':vis'     => isset($data['is_visible']) ? 1 : 0,
        ]);
        return (int) $this->pdo->lastInsertId();
    }
}
?>

控制器處理表單提交

<?php
// public/create.php
require __DIR__ . '/../vendor/autoload.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $repo = new LinkRepository(Database::getPDO());
    try {
        $id = $repo->create([
            'name'         => trim($_POST['name'] ?? ''),
            'url'          => trim($_POST['url'] ?? ''),
            'description'  => trim($_POST['description'] ?? ''),
            'contact'      => trim($_POST['contact'] ?? ''),
            'sort_order'   => (int)($_POST['sort_order'] ?? 0),
            'category_id'  => $_POST['category_id'] ? (int)$_POST['category_id'] : null,
            'is_visible'   => isset($_POST['is_visible']),
        ]);
        header('Location: /list.php?created=1');
        exit;
    } catch (PDOException $e) {
        error_log($e);
        $error = '資料儲存失敗，請稍後再試';
    }
}
// 顯示表單（含錯誤訊息）
include __DIR__ . '/../templates/create_form.php';
?>

表單模板（create_form.php）

<form method="post" action="/create.php">
    

        名稱 
    
    

        網址 
    
    

        描述 
    
    

        聯絡 
    
    

        排序 
    
    

        分類
            
                -- 無 --
                
            
        
    
    

         顯示
    
    送出
    
        

    
</form>

關鍵最佳實踐

• 永不 直接拼接 SQL，全面使用預處理語句
• 連線字元集 utf8mb4 支援完整 Unicode
• ATTR_EMULATE_PREPARES => false 強制真實預處理
• 錯誤模式 ERRMODE_EXCEPTION 統一拋出例外
• 輸出到 HTML 必經 htmlspecialchars()
• 敏感資訊（帳密）放環境變數或設定檔，不入版控