How do I manage tag relationships (Edit/insert)

[conware]

Hi guys.

I need some help with my tags table structure.
Currently I have 3 tables in my phpmyadmin.

Blog (blog_id, title)
Blog_Tags (blog_id, tag_id)
Tags (tag_id, tag)

Now displaying tags next to my blog posts is straightforward.
But how do I manage my tags?

For example if I create a new blog post how do I only insert the new tags into my Tags table but still insert all the tag relations.

And then I have other problem how do i edit my tags.
For example if I start to edit my blog and I change a couple of tags how do I correctly insert the new ones keep the old ones that already there and make sure no double values are inserted into my Tags table.

I had it working but it took me like 20 queries to get the editing done.
And I know for sure thats not the right way to do it.
Then I started from scratch but that didn't work either.
So i'm hoping someone can give me some advice example on what kind queries I should make in order to get this done.

[Old Pedant]

In general, if you use a many-to-many table (in this case, your Blog_Tags table), it is easier to simply delete *all* records that related to the old version and then recreate the entire set.

After all, you must have code (PHP code, I assume?) that analyzes a given blog entry for the first time and extracts the appropriate tags, no? So, after a blog entry is edited, simply first do

DELETE FROM Blog_Tags WHERE blog_id = $blogid

And then use the same code you use for a new blog entry to reestablish the new list of blog tags for this blog.

Example, if the tags are JavaScript, jQuery, AJAX then you could just do

INSERT INTO Blog_Tags (blog_id, tag_id)
SELECT $blogid, tag_id FROM Tags
WHERE tag IN ('JavaScript','jQuery','AJAX')

By far the simplest and most efficient way. Note that using IN( ) as I do here you insert all the relevant records in the Blog_Tags table in a single SQL statement.

[Old Pedant]

Oh, and inserting new tags is easy:

If you make the tag field in the Tags table a UNIQUE INDEX, then simply insert *all* tags using INSERT IGNORE.

That is:

INSERT IGNORE INTO tags ( tag_id, tag ) VALUES( NULL, 'jQuery' )

Again, this means you don't have to first check to see if the tag exists. If it does, you will NOT be creating a duplicate. If it doesn't, the record gets inserted.

In short, use MySQL's features to keep your work to a minimum!

[conware]

Thanks Old Pedant, now I see it's actually pretty simple.
I can't believe I didn't think about using the DELETE statement to remove the relation. Thanks so much for the help.