数据库部分很简单。这只是一个示例,让您了解数据库可能的样子,并不是任何特定的SQL引擎查询。
CREATE TABLE posts (
id INT PRIMARY KEY,
subject VARCHAR(100),
body TEXT
)
CREATE TABLE tags (
id INT PRIMARY KEY,
name VARCHAR(50)
)
CREATE TABLE post_tags (
post_id INT,
tag_id INT,
FOREIGN KEY (post_id) REFERENCES posts (id),
FOREIGN KEY (tag_id) REFERENCES posts (id)
)
为了获取带有“yourTag”标签的项,您只需要运行以下查询:
SELECT P.*
FROM posts P
LEFT JOIN post_tags PT ON (PT.post_id = P.id)
LEFT JOIN tags T ON (T.id = PT.tag_id)
WHERE T.name = 'yourTag';
要获取与帖子ID为123
相关联的标签,您可以运行此查询:
SELECT T.*
FROM tags T
LEFT JOIN post_tags PT ON (T.id = PT.tag_id)
LEFT JOIN posts P ON (PT.post_id = P.id)
WHERE P.id = 123;
对于 PHP 部分,您可以使用框架。许多(如果不是全部)框架都可以轻松地建模这种关系。例如,在 CakePHP 中,可以这样完成:
class Post extends AppModel {
$useTable = 'posts';
$hasAndBelongsToMany = array(
'Tag' => array(
'className' => 'Tag'
'joinTable' => 'post_tags'
'foreignKey' => 'post_id'
'associationForeignKey' => 'tag_id'
)
);
}
class Tag extends AppModel {
$useTable = 'tags';
$hasAndBelongsToMany = array(
'Post' => array(
'className' => 'Post'
'joinTable' => 'post_tags'
'foreignKey' => 'tag_id'
'associationForeignKey' => 'post_id'
)
);
}