Jump to content


Photo

Displaying multiple instances of a field under separate distinct field


  • Please log in to reply
3 replies to this topic

#1 neo926

neo926
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 14 March 2006 - 04:33 PM

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.

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 14 March 2006 - 06:21 PM

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:

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


#3 neo926

neo926
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 14 March 2006 - 06:49 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 March 2006 - 12:46 AM

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

SELECT sport, GROUP_CONCAT( DISTINCT author) AS authors GROUP BY sport

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users