best practise setting up a database for tags

[zhangy]

Hello,

I am wondering whats the best way to store tags from blog posts in a database.

Just stuff tags from each post in a row in the same table as the actual post?

Create a table entirely dedicated to tags? or what?

Ive never done this before. so any help will be appreciated.

thanks.

[kickstart]

Hi

 

Seperate table for tags, one row per tag.

 

If you put all the tags for one blog in one field then it makes it very difficult and inefficient to get the tag details in one SQL operation.

 

Keeping them in a seperate table makes joins to related data far easier.

 

All the best

 

Keith

[Lambneck]

How can I set up said table? Each blog user submits multiple and often unique tags with each of their posts!

[kickstart]

Hi

 

Just something like

 

Id

BlogId

User

TagDetails.

 

Just insert one row for each tag. Can easily be joined back to the full blog to get the details when required.

 

All the best

 

Keith

[zhangy]

so on average people post 3 tag words/phrase with each post are you saying each tag word/phrase should have its own row? (if so i have no idea how to do that) Or just take all the tags from one post and put them in a table row?

[kickstart]

Hi

 

Each tag should have its own row. Major point of a relational database like MySQL is that you can join related rows, something that is very difficult to do if you just lob all the keys together into one field.

 

(if so i have no idea how to do that)

 

You just do an insert rather than updating the row for the post. Without more details of the script you have then not much more I can say.

 

All the best

 

Keith

[zhangy]

well currently each posts tags are being stored in the same row in the same table as the rest of the blog post data. really the part I dont understand is how to seperate the tags from each other.

so for example if the tags of a blog post are submitted like this:

tag1, tag 2, tag3

How to seperate them and store them into individual rows?

[roopurt18]

<?php
$tags = 'tag1, tag2, tag3, ..., tagN';
$tags = explode( ',', $tags );
foreach( $tags as $k => $v ) {
  $v = "'" . mysql_real_escape_string( trim( $v ) ) . "'";
  $tags[$k] = "( {$v} )";
}
$insert = "insert into `tags_table` ( `tag` ) values " . implode( ', ', $tags );
echo $insert;
// You should know what to do from here!
?>

[zhangy]

this may be a dumb question but...

you entered the value of $tags manually.

$tags = 'tag1, tag2, tag3, ..., tagN';

How to get the tags a users entered via the html form as the value of $tags?

[kickstart]

Hi

 

What does your form look like that they have entered the tags on? Is there one input field for all the tags they add? One input field per tag?

 

All the best

 

Keith

[roopurt18]

That depends on your form.

 

You told us they were submitted like tag1, tag2, tag3 so I showed you how to split and insert them if there is a variable holding a string like that.

 

Just replace my $tags = with something like $tags = $_POST['tags'].

[zhangy]

oic. ok now i understanad. thank you both very much!!

[zhangy]

sorry I forgot to ask something. How to connect the tags stored in the tags table with the rest of the blog data stored in the other table? my  tags table looks like what kickstart suggested:

 

Id

BlogId

User

TagDetails.

 

I'm guessing the way to connect them would be to get the Id of the blog post in the BlogTable and insert it into the "BlogId" in the TagsTable. But how?

[kickstart]

I'm guessing the way to connect them would be to get the Id of the blog post in the BlogTable and insert it into the "BlogId" in the TagsTable. But how?

 

Yes, that is what you would do. I would presume that you have the id o the blog post as a field on the form (probably hidden), as you would need it when you update it.

 

If you are inserting a fresh blog post and various tags at the same time then you would insert the blog and gets it id by using mysql_insert_id() and the insert the various tags.

 

All the best

 

Keith

[zhangy]

Like this?:

 

$insert = mysql_query("INSERT INTO $tags_table (blog_id, user, tag) VALUES (".mysql_insert_id().", '$name', ". implode( ', ', $tags ).")");

[kickstart]

Hi

 

Afraid not, as that would just insert a single line (which would probably fail as the number of columns wouldn't match).

 

You would probably need something like this. Insert a record onto the blog table and then loop round inserting tags.

 

<?php
$insert = mysql_query("insert into `blogs_table` ( `blog` ) values ('$blog')");
$BlogId = mysql_insert_id();
$tags = 'tag1, tag2, tag3, ..., tagN';
$tags = explode( ',', $tags );
$name = 'Some Bloggers Name';
foreach( $tags as $k => $v ) 
{
$v = "'" . mysql_real_escape_string( trim( $v ) ) . "'";
$insert = mysql_query("INSERT INTO `tags_table` (blog_id, user, tag) VALUES ($BlogId, '$name', '$v')");
}
// You should know what to do from here!
?>

 

You could also do this which is a variation on the idea from zhangy, but not sure at this level it would be harder for you to follow and maintain. It should be far more efficient though.

 

<?php
$insert = mysql_query("insert into `blogs_table` ( `blog` ) values ('$blog')");
$BlogId = mysql_insert_id();
$tags = 'tag1, tag2, tag3, ..., tagN';
$tags = explode( ',', $tags );
$name = 'Some Bloggers Name';
foreach( $tags as $k => $v ) {
  $v = "'" . mysql_real_escape_string( trim( $v ) ) . "'";
  $tags[$k] = "($BlogId, '$name',  '$v' )";
}
$insert = mysql_query("insert into `tags_table` ( `tag` ) values " . implode( ', ', $tags ));
// You should know what to do from here!
?>

 

Excuse any typos.

 

All the best

 

Keith