How to normalize tags table

I had table like this:

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

what i am trying to do is to normalize it, so i create 2 more tables:

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

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

I have exported all tags from post table to tags table and now each tag has his own id in tags table, but I am so confused when i am thinking how to do it with post_tag table?

edit: questin is how to populate post_tag table, how the query would look like?

tried like this

$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);
      }
    }
}

but it doesn't write complete post_tag table

Answers


Maybe this one will work?

$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);
    }
}

After running the loop, you will need to drop the old column in post table


For every tag a post has, you insert an entry in the post_tag table. It's called an m-to-n relationship. Google it ;)

  • post_id: 1, tag_id: 1
  • post_id: 1, tag_id: 2
  • post_id: 1, tag_id: 3

post

  • post_id

tag

  • tag_id
  • tag_name

post_tag

  • post_id
  • tag_id

I see, you want to solve your issue with one sql-query, but it's not convenient. Try to use php-power and you'll succeed.

The php way. 1. Create tables tags and posts_tags (like you described in the question). 2. In php get all rows from your current post table and loop it:

foreach($query_results as $row) {
  $article_id = $row['article_id];
  $tags = explode('|', $row['tags']);
  foreach($tags as $tag) {
    // here check if this tag exists in your new `tags` table (by tag's name). If it is, save its id in $tag_id variable.
    // if a tag don't exists then insert it. And put its new id (mysql returns it after inserting) into $tag_id.
    // After that add a row ($article_id, $tag_id) to table `posts_tags`
  }
}

Then delete column tags from old post table. Profit!

p.s. Create unique indexes on tag's name in tags table and on pair (article_id, tag_id) in posts_tags table.


Need Your Help

How to avoid PDOStatement::bindParam messing with the referenced value

php mysql pdo phalcon

I'm facing the problem described by Steve M but on a "large scale", please see for details http://www.php.net/manual/en/pdostatement.bindparam.php#94711

Text Labels on Google Maps v3

javascript google-maps google-maps-api-3 google-maps-markers

I recently migrated from v2 to v3 on Google Maps, and one of the functionalities that broke was using text labels, which I was implementing using a third party library (BpLabel)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.