Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts

    How do I manage tag relationships (Edit/insert)

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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
    Code:
    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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    conware (11-02-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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:
    Code:
    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!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    conware (11-02-2012)

  • #4
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •