...

View Full Version : Mysql single query join 3 table and get all the results



jamestave
07-13-2011, 06:49 PM
Hi i want to list all the song for the the album and i want to list all the artist for individual song see below for example.

1. Song Title 1
- Artist 1, Artist 2, Artist 3 note: (all this individual artist have link to there artist page)
2. Song Title 2
- Artist 1, Artist 2
3. Song Title 3
- Artist 1, Artist 2, Artist 3

My tables are song, album, artist, song_artist

http://i.stack.imgur.com/UAukE.jpg

This is my current code I'm using 2 query's but i want to use 1 query to get all the information.


$id =$_GET['id'];
$query = "SELECT id, song_name, FROM song WHERE album_id = '".$id."'";
$result = mysql_query($query) or die("h".mysql_error());
while( $song = mysql_fetch_assoc($result)){
echo $song['song_name'];
$result1 = mysql_query("SELECT artist.artist_name as artist_name, artist.id as aid
FROM artist
INNER JOIN song_artist
ON artist.id = song_artist.artist_id
WHERE song_artist.song_id = '".$song['id']."' ");
while($row = mysql_fetch_array($result1)){
echo "<a href='".$row['sid']."'>".$row['artist_name']."</a>, ";
}
}

how do i write mysql single query to get all the results in php?

Thank you for your help in advance.

James.

Old Pedant
07-13-2011, 10:04 PM
The query is simple enough:


SELECT S.id, S.song_name, A.artist_name
FROM Song AS S, Song_Artist AS SA, Artist AS A
WHERE S.album_id = $album
AND S.id = SA.song_id
AND SA.artist_id = A.id
ORDER BY S.id, A.artist_name

That will get your records such as


101 : Song_Title1 : Artist_2
101 : Song_Title1 : Artist_5
101 : Song_Title1 : Artist_27
102 : Song_Title2 : Artist_3
... etc. ...

Note that you will get *multiple* records PER SONG if a song has more than one artist associated with it.

Now, in your PHP code, you simply have to "dump out" a NEW song title header each time the Song_Title changes. So just remember the prior title (start with maybe $prior_title = "";) and, when it changes, you dump out the new song title and change $prior_title to that title. Then you dump out the artist info, one at a time. Finding a new song title automatically closes a set of artists and starts another set.

I don't code in PHP, but the principle is the same in all languages and should be easy to follow.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum