Jump to content

Archived

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

neuroxik

GROUP BY problem, or maybe not...

Recommended Posts

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):

[b]Oops! ...I Did It Again[/b] [i]<- album name[/i]
Stronger
What U See (is what U get)
Where are you now
[b]In the Zone[/b] [i]<- album name[/i]
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 [b]Select DISTINCT[/b] and then resorted to using [b]GROUP BY album[/b] instead, but the problem I'm getting is that it shows me the following:

[b]Britney[/b]
Anticipating
[b]Greatest Hits[/b]
My Prerogative
[b]In the Zone[/b]
Early Mornin'
[b]Oops! ...I Did It Again[/b]
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.

Share this post


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

Share this post


Link to post
Share on other sites
[!--quoteo(post=376631:date=May 24 2006, 07:31 AM:name=eves)--][div class=\'quotetop\']QUOTE(eves @ May 24 2006, 07:31 AM) [snapback]376631[/snapback][/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 [i]normalize it[/i] 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

Share this post


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

Share this post


Link to post
Share on other sites
[!--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) [snapback]376745[/snapback][/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!

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.