Jump to content

GROUP BY problem, or maybe not...


neuroxik

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.
Link to comment
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
Link to comment
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]
Link to comment
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.