Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Help with retrieving data...

    [PROBLEM SOLVED]

    Code:
    TABLE BLOG - tBlog              TABLE TAGS - tTags
    ---------------------------     ---------------------------
    | blog_id   | blog_tags   |     | tag_id    | tag_name    |
    ---------------------------     ---------------------------
    | 1         | 2,3,5       |     | 1         | html        |
    ---------------------------     ---------------------------
                                    | 2         | php         |
                                    ---------------------------
                                    | 3         | mysql       |
                                    ---------------------------
                                    | 4         | javascript  |
                                    ---------------------------
                                    | 5         | xml         |
                                    ---------------------------
    I'm trying to get all 3 tag names (php, mysql, xml) for blog_id = 1 and the following is what I've tried but it returns only the first tag (php)...

    SELECT tBlog.blog_id, tTags.tag_name FROM tBlog JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags) WHERE tBlog.blog_id = 1

    So please guide me to the correct solution, thanks.
    Last edited by JayQuery; 04-30-09 at 17:38.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you need to rethink your table design
    depending what is in tBlog you may needs to add a 3rd table to act as an intersection table where you associate a blog with a tag

    the association table will contain the relevant data that is unique to the association (often that means just the primary keys of the 'parent' tables).

    if there is no other data in the tBlog table then you could redesign that to allow for multiple entries per blogtag (keep the blogid and have a different row per blogtag. that does mean you couldn't use an autonumber for blogid.

    my guess is this is a cut down table design, so a third (Intersection) table is the way to go.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    I think you need to rethink your table design
    Come on Healdem, you know the design needs to be changed
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2005
    Posts
    30
    Use this query

    SELECT tBlog.blog_id, GROUP_CONCAT(DISTINCT tTags.tag_name
    ORDER BY tTags.tag_name DESC SEPARATOR ', ') FROM tBlog LEFT JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags) WHERE tBlog.blog_id = 1 GROUP BY tBlog.blog_id


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ashish_mat1979
    FROM tBlog LEFT JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags)
    that's not going to work the way you think it will
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2005
    Posts
    30
    Thanks r937 to point out this one is corrected query:

    SELECT tBlog.blog_id, GROUP_CONCAT( DISTINCT tTags.tag_name
    SEPARATOR ', ' ) FROM tBlog, tTags WHERE FIND_IN_SET( tTags.tag_id, tBlog.blog_tags ) >0 AND tBlog.blog_id =1 GROUP BY tBlog.blog_id

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    hint
    ***table redesign***
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2009
    Posts
    6
    Thanks ashish_mat1979, I tried your query but it doesn't work.

    I have created another table to link tBlog and tTags as most of you suggested

    Code:
    BLOG - tBlog              TAGS - tTags              RELATIONSHIPS - tTagRel
    ------------------------  ------------------------  --------------------
    | blog_id | blog_title |  | tag_id | tag_name    |  | blog_id | tag_id |
    ------------------------  ------------------------  --------------------
    | 1       | testing    |  | 1      | html        |  | 1       | 2      |
    ------------------------  ------------------------  --------------------
                              | 2      | php         |  | 1       | 3      |
                              ------------------------  --------------------
                              | 3      | mysql       |  | 1       | 5      |
                              ------------------------  --------------------
                              | 4      | javascript  |
                              ------------------------
                              | 5      | xml         |
                              ------------------------
    The query I'm using...

    SELECT tBlog.blog_title, tTags.tag_name FROM tBlog LEFT JOIN tTagRel ON (tTagRel.blog_id = tBlog.blog_id) LEFT JOIN tTags ON tTags.tag_id = tTagRel.tag_id WHERE tBlog.blog_id = 1

    And fetching data...

    Code:
    $data = mysql_fetch_array($result);
    echo $data['tag_name']; // outputs php ONLY
    I'm pretty sure the query statement is incorrect because it only outputs the first tag.
    So how can I get all 3 containing in an array like $data['tag_name'] = array(0=>'php', 1=>'mysql', 2=>'xml')

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JayQuery
    I have created another table to link tBlog and tTags as most of you suggested
    i would use the same concept (a "many-to-many" relationship table), but i would use the tag name itself instead of a silly integer surrogate

    Code:
    BLOG - tBlog               RELATIONSHIPS - tTagRel   TAGS - tTags    
    ------------------------   ----------------------    --------------- 
    | blog_id | blog_title |   | blog_id | tag_name |    | tag_name    | 
    ------------------------   ----------------------    --------------- 
    | 1       | testing    |   | 1       | php      |    | html        | 
    ------------------------   ----------------------    --------------- 
                               | 1       | mysql    |    | php         | 
                               ----------------------    --------------- 
                               | 1       | xml      |    | mysql       | 
                               ----------------------    --------------- 
                                                         | javascript  | 
                                                         --------------- 
                                                         | xml         | 
                                                         ---------------
    you have a choice, now, in whether to declare the tTags table at all

    -- if you want to control the vocabulary, i.e. to ensure that only approved tag names can be used, then tag_name in tTagRel will be a Foreign Key to tag_name in tTags

    -- if you want to allow any tag names to be used at all (folksonomy), then you would not declare the tTags table

    in both cases, the query to retrieve blogs along with their tag names remains the same

    sweet, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    sweet, eh?
    totally
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2009
    Posts
    6
    Thanks r937, I got your idea.

    Yes, I want to control all the tags because:

    - when writing a post, I wanna display a few popular tags (getting those from tTags) with checkboxes so I could save some time.

    - when I want to edit a tag name, I edit the one in tTags table only, not updating every cell in tTagRel. I think using names instead of numbers is not a good idea in this case.

    And if I used all 3 tags and used names in tTagRel, all the tag names are declared TWICE, isn't that gonna increase the size of mysql data? (say I have 5000 tags or something that contains more than 10 words other than tags)

    I'm not just really trying to solve the tag problem, but I want to learn more about MySQL. So could you please take some time and answer the question in my last post? That is, using my table design and linking all 3 tables to get those tags. Thanks again.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JayQuery
    - when I want to edit a tag name, I edit the one in tTags table only, not updating every cell in tTagRel. I think using names instead of numbers is not a good idea in this case.
    actually, it is

    can you give me an example of a tag that you need to "edit"? and an estimate of how often you will need to do this?

    also, have you ever heard of ON UPDATE CASCADE?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2009
    Posts
    6
    OK, r937, I'm not an expert, I just started to learn MySQL. Like I said, I'm not just trying to solve the tag promblem, but to learn something like joing 3 tables...Yes, a tag in this case could probably never been edited, but it may happen on other cases, so could you please ignore those conditions?

    If you were willing to help, thank you very much and please write me the correct query statement with my table design? (even if you think it's a terribly bad design)

    If you were just trying to pick on those insignificant conditions and showing me how much you know, please, get a cup of coffee and take some rest.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JayQuery
    If you were just trying to pick on those insignificant conditions and showing me how much you know, please, get a cup of coffee and take some rest.
    i was trying to teach you, but that coffee sounds like a great idea, it will warm me up while i wait for you to actually test the query that you wrote and report back what happened
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe this is called "biting the hand that feeds".

    Don't expect the answer on a plate - show a willingness to learn and you will gain a great deal from this community
    George
    Home | Blog

Posting Permissions

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