mysql grouping search results...

General PHP Help
#1

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 . “
” . “’”.$song ."’". "
" . $name . " - " . $sampling_song. “

” . “”;
}
?>

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

#2

Hi there,

Have you tried using the song id?

GROUP BY sampling_songs.sampling_song_id

(remember to put the GROUP BY clause at the end)

#3

Yeah I tried that, as well as many other different GROUP by combinations, but they mostly lead to the same output, rather than grouping the same songs under a single artist, the song AND artist are both printed each time.

#4

The artist would show every time because you are printing it for every row in the loop.

It should be fairly simple to adjust your loop to check if the artist differs from the previous row; if so, print it; if not, don’t print it. Either way you would still print the song details.

Would that work?

#5

Hey that sounds like a good idea, do you have any suggestions as to what kind of loop to use?

Sorry i’m in abit over my head here, I’ve got a faint idea but it hard to structure it…
[hr]

$result=mysql_query($sql);

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

		                echo  $row['artist_name'] . "<br>";
				echo  $row['song_name'] . " <br> ";
		  		echo  $row['sampling_artist_name'] .  " - " . $row['sampling_song_name']. "<br>

}
[hr]

This is what I have so far…

#6

Try this

[php]
$lastArtist = “”;
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’];

if ($lastArtist != $artist) {
echo $artist . “
”;
}
echo “’”.$song ."’". "
" . $name . " - " . $sampling_song. “

” . “”;
$lastArtist = $artist;
}[/php]

#7

Oh, you’d also need to do something similar for the “song” part. Try the first bit and see if it works.

#8

HAHA!! its working ;D ;D

Its now only posting the artist name one (at the beginning) and then printing the songs and sampling_artists/songs below! Thank-you so much!!

Now I guess I can modify the code for songs also in a similar way…Many Thanks!

#9

I now have it working for Songs also :smiley:

Thanks again, I truely appreciate this!

Sponsor our Newsletter | Privacy Policy | Terms of Service