PHP/SQL grouping hitch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PHP/SQL grouping hitch

Author

Topic

gunnercoder
Starting Member

1 Post

Posted - 2011-06-16 : 22:56:52

Hi, I'm currently trying to structure a tricky query involving 5 tables.

The database is based around a Music samples search engine: who has sampled what for which song (if that makes sense...)

These are the tables:
songs, artists, sampling_songs, sampling_artists, sample_refs

The tables contain the following attributes:

SONGS:
song_id
song_name
artist_id*

ARTISTS:
artist_id
artist_name

SAMPLING_SONGS:
sampling_song_id
sampling_song_name
sampling_artist_id*

SAMPLING_ARTISTS:
sampling_artist_id
sampling_artist_name

SAMPLING_REFS:
sample_id
sampling_song_id*
song_id*

(Please note, the SONGS are being sampled by the SAMPLING SONGS..)

I am attempting to write a query which will:

1. Print the name of the artist
2. Print the artist's song
3. Print the name and song of the artists who sampled it

OK, so here is the code I have written so far...

$sql="SELECT sampling_artist_name, sampling_song_name, song_name, artist_name
FROM sampling_artists, sampling_songs, songs, sample_refs, artists
WHERE sampling_artists.sampling_artist_id = sampling_songs.sampling_artist_id
AND sampling_songs.sampling_song_id = sample_refs.sampling_song_id
AND songs.song_id = sample_refs.song_id
AND artists.id = songs.artist_id
AND sampling_artist_name LIKE '%".$search."%'";

$result=mysql_query($sql);

while($row=mysql_fetch_array($result)){

$name=$row['sampling_artist_name'];
$sampling_song=$row['sampling_song_name'];
$song=$row['song_name'];
$artist=$row['artist_name'];

echo $artist . "<br>" . "'".$song ."'". " <br> " . $name . " - " . $sampling_song. "<br><br>" . "";
}
?>

Currently, the output I am getting is the following...

Artist: The Bar-Kays
Song: 'Humpin''
Sampled By: Cypress Hill - Real Estate

Artist: The Bar-Kays
Song: 'Humpin''
Sampled By: Cypress Hill - Funky Cypress Hill Sh*t

Artist: Gary Burton Quartet
Song: 'Las Vegas Tango'
Sampled By: Cypress Hill - Illusions

Not too bad really, but notice the duplicate posting of "Humpin" by the Barkays...This always happens when a song is sampled multiple times..
I am trying to find a way of grouping the data so that both the output will be more like this..

Artist: The Bar-Kays
Song: 'Humpin''
Sampled By: Cypress Hill - Real Estate
Sampled By: Cypress Hill - Funky Cypress Hill Sh*t

In other words, i wish to eliminate the duplicate posting by grouping the SONGS together, rather than reposting for each instance of a sample. I have tried GROUP BY song_name, as well as a few others but no luck

Sorry for rambling, any help would be awesome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-08 : 05:04:30

This is more like fromatting issue which you should do in PHP.
Alternatively you can concatenate "sampled by" column values for each song. Have a look at group_concat function in Mysql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources