PHP MySQL 資料庫

PHP 可以連接 MySQL 資料庫進行資料的新增、讀取、更新和刪除 (CRUD) 操作。

連接方式

PHP 提供兩種連接 MySQL 的方式：

• MySQLi：MySQL Improved，專為 MySQL 設計
• PDO：PHP Data Objects，支援多種資料庫

PDO 連接

<?php
$host = 'localhost';
$dbname = 'myapp';
$username = 'root';
$password = 'secret';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
    echo "連接成功";
} catch (PDOException $e) {
    die("連接失敗：" . $e->getMessage());
}
?>

查詢資料

<?php
// 簡單查詢
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo $user['name'] . "\n";
}

// 取得單筆
$stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
$user = $stmt->fetch();
?>

預處理語句（防止 SQL Injection）

<?php
// 使用 ? 佔位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();

// 使用命名佔位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => 'alice@example.com']);
$user = $stmt->fetch();

// 多個參數
$stmt = $pdo->prepare("SELECT * FROM users WHERE age >= :min AND age <= :max");
$stmt->execute(['min' => 18, 'max' => 65]);
$users = $stmt->fetchAll();
?>

新增資料

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['Alice', 'alice@example.com']);

// 取得新增的 ID
$newId = $pdo->lastInsertId();
echo "新增成功，ID: $newId";
?>

更新資料

<?php
$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->execute(['Bob', 1]);

// 取得影響的列數
$affected = $stmt->rowCount();
echo "更新了 $affected 筆資料";
?>

刪除資料

<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);

$affected = $stmt->rowCount();
echo "刪除了 $affected 筆資料";
?>

交易處理

<?php
try {
    $pdo->beginTransaction();
    
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    $pdo->commit();
    echo "交易成功";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "交易失敗：" . $e->getMessage();
}
?>

MySQLi 連接

<?php
$mysqli = new mysqli('localhost', 'root', 'secret', 'myapp');

if ($mysqli->connect_error) {
    die("連接失敗：" . $mysqli->connect_error);
}

// 設定字元集
$mysqli->set_charset('utf8mb4');

// 查詢
$result = $mysqli->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}

// 預處理語句
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

$mysqli->close();
?>

實際範例

<?php
class Database {
    private static ?PDO $instance = null;
    
    public static function getConnection(): PDO {
        if (self::$instance === null) {
            self::$instance = new PDO(
                "mysql:host=localhost;dbname=myapp;charset=utf8mb4",
                'root',
                'secret',
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]
            );
        }
        return self::$instance;
    }
}

class UserRepository {
    private PDO $db;
    
    public function __construct() {
        $this->db = Database::getConnection();
    }
    
    public function findAll(): array {
        return $this->db->query("SELECT * FROM users")->fetchAll();
    }
    
    public function findById(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }
    
    public function create(array $data): int {
        $stmt = $this->db->prepare(
            "INSERT INTO users (name, email) VALUES (?, ?)"
        );
        $stmt->execute([$data['name'], $data['email']]);
        return (int) $this->db->lastInsertId();
    }
}
?>