normalize database, populate map table

[inTry]

Hi all,

 

I am trying to normalize my database

 

I had table POST where the all tags have been saved (comma separated):

 

POST:

 

article_id

      all_tags

1

    x1, x2, x3

2

    x2, x3, x4

 

 

now I create new table TAGS and insert all tags from POST table in single column

 

TAGS:

tag_id

    tag_name

1

      x1

2

      x2

3

      x3

 

I made POST_TAG (as map table) table but i have no idea how to populate it,  how the query should look like, how to?

 

POST_TAG:

id_post

      id_tag

1

      1

1

      2

2

      1

 

thank you

 

[Jessica]

I think for a one time update like this, you'll need to use PHP to parse the existing data and generate the new inserts.

[inTry]

$sql=mysql_query("SELECT * FROM post");	
while($row=mysql_fetch_array($sql)) 
	{
		$article_id=$row['article_id'];
		$all_tags=$row['all_tags'];		
		$all_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'];

								echo $tag_id . "<br>";									
								$q = "INSERT INTO post_tag (id_post, id_tag) VALUE ('$article_id', '$tag_id')";
								$r = mysql_query($q);		

								}		
					}
	}

 

I tried something like this, but it doesn't write all post_tag table

[shlumph]

That looks like it should work.  Try debugging, maybe it's failing somewhere:

$r = mysql_query($q) or die(mysql_error() . '<br />' . $q);

[fenway]

You can do all of this in MySQL with a JOIN and INSERT INTO .. SELECT WHERE.

[Barand]

VALUE (...) should be VALUES (...)

[Jessica]

You can do all of this in MySQL with a JOIN and INSERT INTO .. SELECT WHERE.

How? The data is x1, x2, x3

Would you do it using the LIKE(% %)?

[Barand]

Scratching my head over that statement too. Enlighten us, Si-Fu.

[fenway]

Well, it would require a number of self-joins and some fore-knowledge of the max number of values in the list.

[Barand]

OK. So let's say the maximum is 3 ???

[fenway]

Well, for starters:

 

SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( post.alltags, ',', 1 ), ',', -1 ) AS tag1
,SUBSTRING_INDEX( SUBSTRING_INDEX( post.alltags, ',', 2 ), ',', -1 ) AS tag2
,SUBSTRING_INDEX( SUBSTRING_INDEX( post.alltags, ',', 3 ), ',', -1 ) AS tag3
FROM post