萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 連接 MySQL 完整教學:從建立資料庫到 PDO 安全查詢最佳實踐

建立一個通訊錄資料庫,從建表、連線、查詢到安全輸出,完整演示 PHP 操作 MySQL 的標準流程。棄用 mysql_* 函數,全程採用 PDO (PHP Data Objects),支援預處理語句、多資料庫、異常處理,是現代 PHP 專案的標準選擇。

第一步:建立資料庫與資料表

-- 建立資料庫
CREATE DATABASE `txl` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `txl`;

-- 建立通訊錄表
CREATE TABLE `personal_info` (
    `pi_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵 ID',
    `pi_name` VARCHAR(50) NOT NULL COMMENT '姓名',
    `pi_tel` VARCHAR(20) DEFAULT NULL COMMENT '電話',
    `pi_qq` VARCHAR(20) DEFAULT NULL COMMENT 'QQ 號',
    `pi_email` VARCHAR(100) DEFAULT NULL COMMENT '電子郵件',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`pi_id`),
    INDEX `idx_name` (`pi_name`),
    INDEX `idx_email` (`pi_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通訊錄資料表';

-- 插入測試資料
INSERT INTO `personal_info` (`pi_name`, `pi_tel`, `pi_qq`, `pi_email`) VALUES
('張三', '13911111111', '642864125', 'zhangsan@126.com'),
('李四', '13122222222', '63958741', 'lisi@163.com'),
('王五', '13833333333', '912345678', 'wangwu@sohu.com');

第二步:PDO 連線配置 (config.php)

<?php
// config.php - 不提交至版控,生產環境由環境變數注入
return [
    'db' => [
        'host' => $_ENV['DB_HOST'] ?? '127.0.0.1',
        'port' => (int)($_ENV['DB_PORT'] ?? 3306),
        'name' => $_ENV['DB_NAME'] ?? 'txl',
        'user' => $_ENV['DB_USER'] ?? 'root',
        'pass' => $_ENV['DB_PASS'] ?? '',
        'charset' => 'utf8mb4',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,      // 拋出異常
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 返回關聯陣列
            PDO::ATTR_EMULATE_PREPARES => false,              // 使用原生預處理
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
        ],
    ],
];

第三步:資料庫連線服務 (Database.php)

<?php
// Database.php
class Database {
    private static ?\PDO $instance = null;

    public static function getConnection(array $config): \PDO {
        if (self::$instance === null) {
            $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['name']};charset={$config['charset']}";
            self::$instance = new \PDO($dsn, $config['user'], $config['pass'], $config['options']);
        }
        return self::$instance;
    }

    // 防止複製/序列化
    private function __construct() {}
    private function __clone() {}
    public function __wakeup() { throw new \Exception("Cannot unserialize"); }
}

第四步:資料存取層 (Repository 模式)

<?php
// ContactRepository.php
class ContactRepository {
    public function __construct(private \PDO $pdo) {}

    public function findAll(): array {
        $stmt = $this->pdo->query("SELECT pi_id, pi_name, pi_tel, pi_qq, pi_email FROM personal_info ORDER BY pi_id");
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM personal_info WHERE pi_id = ?");
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function create(array $data): int {
        $stmt = $this->pdo->prepare("INSERT INTO personal_info (pi_name, pi_tel, pi_qq, pi_email) VALUES (?, ?, ?, ?)");
        $stmt->execute([$data['name'], $data['tel'], $data['qq'], $data['email']]);
        return (int)$this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool {
        $fields = [];
        $params = [];
        foreach (['name', 'tel', 'qq', 'email'] as $field) {
            if (isset($data[$field])) {
                $fields[] = "pi_{$field} = ?";
                $params[] = $data[$field];
            }
        }
        if (!$fields) return false;
        $params[] = $id;
        $stmt = $this->pdo->prepare("UPDATE personal_info SET " . implode(', ', $fields) . " WHERE pi_id = ?");
        return $stmt->execute($params);
    }

    public function delete(int $id): bool {
        $stmt = $this->pdo->prepare("DELETE FROM personal_info WHERE pi_id = ?");
        return $stmt->execute([$id]);
    }

    public function search(string $keyword): array {
        $stmt = $this->pdo->prepare("SELECT * FROM personal_info WHERE pi_name LIKE ? OR pi_tel LIKE ? OR pi_email LIKE ?");
        $like = "%{$keyword}%";
        $stmt->execute([$like, $like, $like]);
        return $stmt->fetchAll();
    }
}

第五步:控制器/腳本輸出 HTML 表格 (list.php)

<?php
// list.php
require 'vendor/autoload.php'; // 若使用 Composer
require 'config.php';
require 'Database.php';
require 'ContactRepository.php';

$config = require 'config.php';
$pdo = Database::getConnection($config['db']);
$repo = new ContactRepository($pdo);
$contacts = $repo->findAll();
?>



    
    通訊錄列表
    


通訊錄管理

新增聯絡人 搜尋
ID姓名電話QQEmail操作
編輯 | 刪除

關鍵安全與最佳實踐總結

  1. 參數化查詢:所有動態條件使用 ? 或 :name 佔位符,execute([$val]) 綁定參數,徹底防 SQL 注入
  2. 字符集 utf8mb4:支援完整 Unicode (含 Emoji),資料庫、表、連線、HTML 頁面全鏈路統一
  3. 錯誤模式:PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,統一 try-catch 處理,避免 die()/exit() 洩露堆疊
  4. 最小權限帳號:應用帳號僅擁有必要表的 CRUD 權限,無 DROP/ALTER/GRANT
  5. 輸出轉義:HTML 輸出務必 htmlspecialchars() 防 XSS
  6. 配置分離:資料庫憑證放 .env 或環境變數,絕不寫死在代碼
  7. 連線複用:單例模式或 DI 容器管理 PDO 實例,避免重複連線

進階擴充:分頁、排序、匯出


// 分頁查詢
public function findPaginated(int $page, int $perPage, string $orderBy = 'pi_id', string $orderDir = 'DESC'): array {
    $offset = ($page - 1) * $perPage;
    $stmt = $this->pdo->prepare("SELECT * FROM personal_info ORDER BY {$orderBy} {$orderDir} LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// 總筆數
public function countAll(): int {
    return (int)$this->pdo->query("SELECT COUNT(*) FROM personal_info")->fetchColumn();
}

結語

從傳統 mysql_* 遷移至 PDO + 預處理語句 + Repository 模式,是 PHP 專案邁向專業、安全、可維護的關鍵一步。本文提供的完整代碼結構可直接作為中小型專案的起始模板,並易於擴展至 Laravel/Eloquent、Doctrine ORM 等進階框架。

copyright © 萬盛學電腦網 all rights reserved