php - 如何标准化标签表

Question

我有这样的表:

post:
+----------------+-----------+
| article_id     | tags      | 
+----------------+-----------+
| 1              |  x1,x2,x3| 
| 2              |  x1,x4,x5 | 
+----------------+-----------+

我想做的是标准化它，所以我创建了另外 2 个表:

tags:
+----------------+-----------+
| tag_id         | tag_name  | 
+----------------+-----------+
| 1              |    x1     | 
| 2              |    x2     | 
| 3              |    x3     |
+----------------+-----------+

post_tag:
+----------------+-----------+
| id_post         | id_tag  | 
+----------------+-----------+
| 1              |    1     | 
| 1              |    2     | 
| 2              |    1     |
+----------------+-----------+

我已经将所有标签从post表导出到tags表，现在每个标签在tags表中都有自己的id，但是当我思考如何使用post_tag表来做到这一点时，我很困惑？

编辑:questin是如何填充post_tag表的，查询会是什么样子？

尝试过这样

$sql=mysql_query("SELECT * FROM post"); 
    while($row=mysql_fetch_array($sql)) {
        $article_id=$row['article_id'];
  $all_tags =$row['tags'];      
  $tags= explode(",",$all_tags);                                
    foreach($all_tags as $tag) {
     $sql2=mysql_query("SELECT * FROM tags WHERE tag_name = '$tag'");
      while($row=mysql_fetch_array($sql2)) {                               $tag_id=$row['tag_id'];                                  $q = "INSERT INTO post_tag (id_post, id_tag) VALUE ('$article_id', $tag_id')";
        $r = mysql_query($q);
      }
    }
}

但它没有写入完整的 post_tag 表

Answer 1

也许这个会起作用？

$sql= mysql_query("SELECT * FROM post"); 
while($row=mysql_fetch_array($sql)) {
    $article_id=$row['article_id'];
    $all_tags = $row['tags'];      
    $tags = explode(",",$all_tags);              
    foreach($all_tags as $tag_title) {
        $tag_id = null;
        $tag=mysql_fetch_assoc(mysql_query('SELECT * FROM tags WHERE tag_name = "'.$tag_title.'"'));
        if ($tag) {
            $tag_id = $tag['tag_id']; 
        } else {
            mysql_query('INSERT INTO tags SET tag_name = "'.$tag_title.'"');
            $tag_id = mysql_insert_id();
        } 
        $q = "INSERT INTO post_tag (id_post, id_tag) VALUE ('$article_id', $tag_id')";
        $r = mysql_query($q);
    }
}

运行循环后，您需要删除 post 表中的旧列