建立一個通訊錄資料庫,從建表、連線、查詢到安全輸出,完整演示 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');
<?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",
],
],
];
<?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"); }
}
<?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();
}
}
<?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();
?>
通訊錄列表
? 或 :name 佔位符,execute([$val]) 綁定參數,徹底防 SQL 注入PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,統一 try-catch 處理,避免 die()/exit() 洩露堆疊htmlspecialchars() 防 XSS.env 或環境變數,絕不寫死在代碼
// 分頁查詢
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 等進階框架。