Displaying multiple instances of a field under separate distinct field

[neo926]

What a fun topic name that is. Okay, this is the last big challenge for me and my site. As I've said in my earlier threads, I have a db that contains columns that are posted for a sports site. One of the fields in my table that contains the column information is the specific sport being written about. Another field contains the name of the author of the column. So, what I would like to do is query the db to achieve the following display result:

Table contains:

Sport Author
NFL Mark
MLB Joe
NFL Matt
NBA John
MLB Steve

Query displays:

NFL
Mark
Matt

MLB
Joe
Steve

NBA
John

Now I know I can use the SELECT DISTINCT(sport) to display each sport individually. But can this be used in conjunction somehow with another function to get the names of the authors of said sport to display underneath? I would appreciate any help on this matter.

[wickning1]

Best you can do is use ORDER BY sport, author

That way you get:

MLB Joe
MLB Steve
NBA John
NFL Mark
NFL Matt

You'll have to do some post processing in PHP to make sure you print, for instance, MLB only once. Something like:

[code]while ($row = mysql_fetch_assoc()) {
   if ($save_sport != $row['sport']) {
      echo $row['sport'] . "<br>\n";
      $save_sport = $row['sport'];
   }
   echo $row['author'] . "<br>\n";
}[/code]

[neo926]

You have totally just jumped about 1,000 spots up on my 'Cool' list. Outstanding! Thank you so much!

[fenway]

Actually, the "best" you can do is to use the GROUP_CONCAT() function (4.1+):

[code]SELECT sport, GROUP_CONCAT( DISTINCT author) AS authors GROUP BY sport[/code]

Hope that helps.