SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table set up for tag system

    Currently I store the tags with the corresponding post in a "tag1, tag2, three word tag, tag4" format, separated by commas. I was planning on searching using FULLTEXT but found by default it has a three word minimum and tags that are more than two words are not searched as "one word" I was wondering how to develop a system to relate tags whether they are one word or not. Will I need a post and tag table or a post, tag and one other table? At the moment I'm not worried about tag clouds, but if i could set it up for future ideas would be nice.

    I'm really stuck on the insert query too if it is more than one table. If I tag two different posts with a same tag, do I still insert the tag as a new row or link the same row to two posts?
    Half way to nowhere

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    "I store the tags", you say that like we are supposed to know what you mean! Let me take a guess though:

    Do you mean your editors are allowed to add meta tags (much like a keyword) that describe facets of an article?

    If so, shouldn't you be storing the individual "meta tags" in their own table?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    three word minimum? haven't heard of that one -- do you mean the four-letter-word minimum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just wrote a reply on http://www.sitepoint.com/forums/showthread.php?t=502206 for setting up the tables also given the code refer to it...

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, sorry for not being completely clear. This is for a blog and for each post it will be tagged with around three words that relate to the article, not meta tags though. Yes I meant four letter minimum, I meant to say words with three or less letters are ignored by default with FULLTEXT. That tags that are two words are not searched as one word with FULLTEXT. So I was thinking of storing the tags into a table as their own row of a tags table. If you are reading a post and click one of the tags it will then display all articles with that tag. I'm just confused on how you can relate one tag to multiple posts.
    Half way to nowhere

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by atomicnuke View Post
    So I was thinking of storing the tags into a table as their own row of a tags table. If you are reading a post and click one of the tags it will then display all articles with that tag. I'm just confused on how you can relate one tag to multiple posts.
    that's the "toxi" solution described here:
    http://www.pui.ch/phred/archives/200...e-schemas.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Great link r937.

    @atomicnuke
    In answer to your question, read the link suggested - but in simple terms lets say your article id number 37 (about greyhound racing) has been tagged with the following words ( or even phrases if you wish) :

    101 ("dog")
    206 ("fast").

    so you would create a referential table containing something like:

    article_ref , tag_ref
    ==============
    37 | 101
    37 | 206

    to find other articles tagged with word number 101 is a simple sql query joined to the titles of the articles.

  8. #8
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks to be the setup I'd be after, just a few quick insights.

    1. Duplicate tags would have to be a new row though right in the tag table, not unique?
    2. Or is the tag table unique and the tagmap is the one the grows accordingly? So the tag table only grows as new tags are added.
      tagmap
      id: 1 bookmark_id: 1 tag_id: 1
      id: 2 bookmark_id: 2 tag_id: 1
    Half way to nowhere

  9. #9
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You beat me to what I was trying to ask, so the second one I was asking is what I should be doing. One last question I hope is the INSERT query I'd insert the blog post easily. But having troubles seeing how I'd actually do the inserts on the other two. I'd have to get the blog post id, but how do you know the id of the tags if you have more than one tag to insert into the tagmap table. I'm still assuming the tags are UNIQUE and will inserting say three tags and one of them is already in the table, does MySQL just skip that tag or stop dead and not insert the other two tags?
    Half way to nowhere

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    posts are unique -- this is easy, the post_id is the PK in the posts table

    tags are unique -- this is easy, the tag_id is the PK in the tag table

    the tags on a given post are unique -- this is not always obvious (because some people use an auto_increment in theis table, which is wrong), the two FKs combine to form a composite PK on the posttags (tagmap) table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry not completely understanding PK and FK?
    Half way to nowhere

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    PK = primary key, FK = foreign key

    if the author of the article i linked to in post #6 had used FKs, he would never have said:
    Disadvantages:

    When altering or deleting bookmarks you can end up with tag-orphans.
    because with FKs that is not possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still a little lost sorry for making this complicated. So the tag table shouldn't be auto_incremented or the tagmap? So if a post as three tags do all the tags get put into the same row or do they each get put into their own row? Meaning three inserts... If this is the case I don't see the benefit?
    Half way to nowhere

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    create table posts
    ( id    integer     not null primary key auto_increment
    , title varchar(99) not null
    , body  text        not null
    );
    create table tags
    ( id   smallint    not null primary key auto_increment
    , name varchar(99) not null
    );
    create table posttags
    ( postid  integer not null 
    , tagid   smallint not null 
    , foreign key ( postid ) references posts ( id )
    , foreign key ( tagid ) references tags ( id )
    , primary key ( postid, tagid )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    posts
    1042 broadband in katmandu
    1043 what about the gerbils
    1044 i can has cheezburger

    tags
    23 technology
    24 my pets
    25 travel
    26 humour

    posttags
    1042 23
    1042 25
    1043 24
    1044 24
    1044 26

    EDIT: 20,000th post!!!
    Last edited by r937; Sep 9, 2007 at 17:58.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to understand the INSERT queries I understand getting the last insert id for the posts. But if the tags go into a row by themselves do i have to get that insert id then insert on the tagmap table and keep looping until all tags are inserted seems like a lot of inserts if that's how you do it. Also, if you have a duplicate tag name does it skip it on UNIQUE columns or does it stop the query dead.

    How I understand you do it is for a post with 3 tags, that is six inserts?
    Half way to nowhere

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's take an example

    to start, let's say your 3 tables are already defined

    consider the form which you use to enter a blog post, and let's imagine that it includes a dropdown list for your tags...

    <select name="tags" multiple="multiple">
    <option value="23">technology</option>
    <option value="24">my pets</option>
    <option value="25">travel</option>
    <option value="26">humour</option>
    </select>

    now, using your scripting language (asp, php or whatever), you figure out which tags have been selected for the the post you're entering

    then you would

    1. insert the post
    1a. obtain the id value, using the LAST_INSERT_ID function (let's say for the sake of the example it's 1056)
    2. insert the tags (let's say for the sake of the example that 23, 25, and 26 were selected) with the following --

    INSERT INTO posttags VALUES (1056,23), (1056,25), (1056,26)

    see how that works?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'm getting closer, but lets say it isn't a drop down list. Because some tags will be new ones made for that post. I enter them in a text/texarea field whichever separated by commas, then exploded into an array. So if they are three new tags is there a way to get all three id's after the insert or do I have to do three queries and get the id after each one? Also, does a UNIQUE field and trying to insert a same value stop the query when inserting three values or does it just skip and try the next one? Sorry for making this post drag on, but a three table setup is completely new to me.
    Half way to nowhere

  19. #19
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by atomicnuke View Post
    Okay, I'm getting closer, but lets say it isn't a drop down list. Because some tags will be new ones made for that post. I enter them in a text/texarea field whichever separated by commas, then exploded into an array. So if they are three new tags is there a way to get all three id's after the insert or do I have to do three queries and get the id after each one?
    It sounds as though you haven't yet worked out how you would select multiple tags for a story ...

    This is all GUI work you need to work on. Wouldnt you have a system of reminding yourself of the current tags you have? Like a list you pick from?

    Then you need to work out how will your program will handle new entries into the tags table?

    There will be varying ways of doing this. If you and and a handful of editors are logged in and working on this tagging system, then using Ajax might be a good bet.

    For simplicity though, just do mulitple page refreshes for each new word, then get jiggy with it later. Just get it working first.

    You seem to be suggesting a free text box you just write tags into. That will magically go away and for each tag not in your database, add a new row and assign that new id number to the story you are working on.

    I'm not saying that isnt doable, I just dont think its a good idea, you will end up with tags like:
    "pet" , "pets" , "my pet", "my pets" moving away from the idea of a controlled vocabulary into your own organised chaos.

    Quote Originally Posted by atomicnuke View Post
    Also, does a UNIQUE field and trying to insert a same value stop the query when inserting three values or does it just skip and try the next one?
    Trying to add a duplicate to a Unique field returns a mysql error number (1067 I think). You have to program to handle that error.

    Quote Originally Posted by atomicnuke View Post
    Sorry for making this post drag on, but a three table setup is completely new to me.
    Google for Database Normalization if you would like more explanations.

    This thread from yesterday might shed some light on what you are trying to achieve too.

  20. #20
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Cups, never thought of it that way, does make sense. No I see how to go about it and I think I got it now. Also, big thanks to r937 for talking the time and patience to help me understand the concept.
    Half way to nowhere

  21. #21
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I'm back again, I got the interface and insert method working now I'm trying to get the data out with a query.

    Here is what I got so far:
    PHP Code:
    SELECT blog.idblog.rtitletags.tag
    FROM blog
    LEFT JOIN posttags ON blog
    .id posttags.postid
    LEFT JOIN tags ON posttags
    .tagid tags.id
    WHERE blog
    .id =
    I'm getting the data I need, but for multiple tag entries that means I have multiple rows, it suppose to be that way?
    Half way to nowhere

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it is

    a result set is always tabular

    therefore if there's a one-to-many relationship (it's actually a many-to-many relationship, but only one-to-many since we're restrincting to blog 4), therefore the blog columns will "repeat" once for each tag

    there are two ways to handle this -- by detecting the multiples when looping over the results with your scripting language, or by "collapsing" the tags into a single value

    the latter is a viable approach only if there is a small number of columns (one or two) returned for the "many" side of the relationship
    Code:
    SELECT blog.rtitle, GROUP_CONCAT(tags.tag) as tags
    FROM blog 
    LEFT JOIN posttags ON blog.id = posttags.postid 
    LEFT JOIN tags ON posttags.tagid = tags.id 
    WHERE blog.id =4 
    GROUP BY blog.id
    note that there is only one blog row selected, so to GROUP BY that might seem needless, but the syntax requires it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So the looping approach you'd recommend if I have an article with three of more tags or many results if I don't restrict it by blog id? I've looped with while($rows = mysql_fetch_array($result)) With one article you know you can loop through the tags because they belong to the same article, but how would you if you have multiple articles?
    Half way to nowhere

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would make the decision based on how many columns from the "many" table i wanted -- one or two, use GROUP_CONCAT, more than two, handle it in the script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, why do you say not to use the function if there are more than 2 columns? Does it slow down performance?


Bookmarks

Posting Permissions

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