Jump to content


Photo

GROUP BY problem, or maybe not...


  • Please log in to reply
4 replies to this topic

#1 neuroxik

neuroxik
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 24 May 2006 - 09:22 AM

Okay, here's my issue in plain english:
I'm making a music lyrics site. So far the users can browse by artist and get the resulting song titles when an artist is clicked. I'll take Britney Spears (hehe) for this example. What I want now is the user to have the option to browse the artist's song by album name (because I do have a column in the mysql db named 'album') and here's what I'd like to get (briefly):

Oops! ...I Did It Again <- album name
Stronger
What U See (is what U get)
Where are you now
In the Zone <- album name
Early Mornin'
Everytime
(I got that) Boom Boom
Me against the music
Toxic

Of course, I don't want the name of the album to be repeated, so I tried Select DISTINCT and then resorted to using GROUP BY album instead, but the problem I'm getting is that it shows me the following:

Britney
Anticipating
Greatest Hits
My Prerogative
In the Zone
Early Mornin'
Oops! ...I Did It Again
Dear Diary

As you can see, it does not repeat the name of the album, but it also only shows only 1 song per album, here's what I wrote for that:
"SELECT * FROM lyricstable WHERE artist='$a' GROUP BY album ORDER BY album ASC"
note that my $a is from my $_GET for the artist name.

Does anyone know how to achieve what I illustrated in the first diagram ?
Thx in advance.

#2 eves

eves
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 May 2006 - 11:31 AM

are you only using a single table (lyricstable)?
if yes, you should normalize it first before proceeding
if not, please post the structure of the other tables



#3 neuroxik

neuroxik
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 24 May 2006 - 02:04 PM

[!--quoteo(post=376631:date=May 24 2006, 07:31 AM:name=eves)--][div class=\'quotetop\']QUOTE(eves @ May 24 2006, 07:31 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
are you only using a single table (lyricstable)?
if yes, you should normalize it first before proceeding
if not, please post the structure of the other tables
[/quote]

Uhm, I don't get the normalize it first [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]
Yes, I'm using only one table called lyricstable, with 5 fields
id, artist, album, title, lyrics

#4 freakus_maximus

freakus_maximus
  • Members
  • PipPipPip
  • Advanced Member
  • 177 posts

Posted 24 May 2006 - 05:23 PM

Check out my reply on this thread using the "ORDER BY" condition in your SELECT statement.

You have not posted any other code, so I cannot tell how you have your WHILE statement setup, but take a look at my example, it should get you going in the direction.

[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=94066&st=0&p=376462&#entry376462\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?...62&#entry376462[/a]

#5 neuroxik

neuroxik
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 24 May 2006 - 08:58 PM

[!--quoteo(post=376745:date=May 24 2006, 01:23 PM:name=freakus_maximus)--][div class=\'quotetop\']QUOTE(freakus_maximus @ May 24 2006, 01:23 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Check out my reply on this thread using the "ORDER BY" condition in your SELECT statement.

You have not posted any other code, so I cannot tell how you have your WHILE statement setup, but take a look at my example, it should get you going in the direction.

[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=94066&st=0&p=376462&#entry376462\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?...62&#entry376462[/a]
[/quote]


EDIT: It worked out dude! I forgot to come back for some small praise and thanks, but thanks a hell lot!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users