设计高效的PHP数据库通常涉及以下几个关键步骤和原则:
假设我们要设计一个简单的博客系统,包含用户表、文章表和评论表。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE comments (
id INT AUTO_INCREMENT PRIMARY KEY,
article_id INT NOT NULL,
user_id INT NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (article_id) REFERENCES articles(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
<?php
$dsn = 'mysql:host=localhost;dbname=blog';
$username = 'root';
$password = '';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 创建文章
$stmt = $pdo->prepare("INSERT INTO articles (user_id, title, content) VALUES (?, ?, ?)");
$stmt->execute([1, 'Sample Title', 'Sample Content']);
// 获取文章
$stmt = $pdo->prepare("SELECT * FROM articles WHERE id = ?");
$stmt->execute([1]);
$article = $stmt->fetch(PDO::FETCH_ASSOC);
// 添加评论
$stmt = $pdo->prepare("INSERT INTO comments (article_id, user_id, content) VALUES (?, ?, ?)");
$stmt->execute([$article['id'], 1, 'Great article!']);
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
通过以上步骤和示例,你可以设计出一个高效、安全的PHP数据库。