Jump to content

Archived

This topic is now archived and is closed to further replies.

neo926

Displaying multiple instances of a field under separate distinct field

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
You have totally just jumped about 1,000 spots up on my 'Cool' list. Outstanding! Thank you so much!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.