SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Group Query Results

    I have searched both the PHP and MySQL forums here for an answer, and it seems like while a lot of people have asked the same question, there hasn't yet been a complete answer. So, I am hoping you can help me out...

    I am using a JOIN query to get magazine titles and their tags. I have a one-to-many table relationship, and so the query result comes out like this:

    $title - $tag
    ===============
    MagazineA - Cooking
    MagazineA - Home
    MagazineA - Food
    MagazineB - Sports
    MagazineB - Fitness

    So the magazine title is repeating. From my understanding, this is a normal result from using such a query. However, I'd like to show the title only once, followed by its tags, like so:

    MagazineA - Cooking, Home, Food
    MagazineB - Sports, Fitness

    In many threads with a similar question, people have answered with things like "Oh, that's easy... Just use an inner loop" or something along those lines. Problem is, I don't know PHP well enough to be able to translate an answer like that into code.

    Can someone please help by telling me how the code would look to make it work? Thank you very much in advance!

    Edit: Here's an example of a thread that I found (one of many) with the same issue. The solution is there, but I can't make any sense of it.
    http://www.sitepoint.com/forums/show...-to-many+query

  2. #2
    SitePoint Guru silver trophy JamesColin's Avatar
    Join Date
    May 2009
    Location
    Jomtien, Pattaya, Thailand
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I can give you pseudo code:
    Code:
    $title = "";
    while ($row = read($sql_result)) {
     $rowtitle = $row['title'];
     // if the title is different from what we already have, then print it and update what we have, otherwise don't print it
     if $rowtitle != $title {
      echo "<br />".$rowtitle;
      $title = $rowtitle;
     }
     echo $row['tag'];
    }
    Do you really need traffic? Where to? What for?
    If you really do need traffic then stop messing around!
    Advertise on my sites today: She Told Me & Best Reviewer :
    200,000+ UV / Month

  3. #3
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With this kind of output you can use GROUP BY statement and group_concat() function in your query.

  4. #4
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    JamesColin - Thank you so much! Can't believe it's that simple.

    Shrapnel_N5 - I did find a solution using group_concat(), but they said (I believe it was r397) that it shouldn't be used on more than two or three rows (so, in this case, if there will be more than three tags it won't be ideal to use it). Why? I have no clue.

    If anyone knows of alternative methods, please share! This seems to be a common question and there aren't many answers to it out there.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    GROUP_CONCAT shouldn't be used on more than three rows?

    couldn't've been me

    either you use GROUP_CONCAT or you don't

    for tags, it would be okay, since tags are usually short, and the concatenated results won't be ~too~ long

    for post comments, on the other hand, it wouldn't
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it isn't common nor complicated one to waste time in search for "perfect ever" solution.
    Nobody cares. That's why aren't many answers.
    There Is More Than One Way To Do It
    Use whatewer you like.

    Grouping with PHP is more flexible. Especially if you need to output more details of goods.
    But, as I said above, in this very case you can use SQL.

  7. #7
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Common or uncommon, thanks for the help anyway (and explanations). For some reason I just couldn't wrap my mind around it, although in hindsight it seems so simple.


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
  •