Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mysql single query join 3 table and get all the results

    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



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

    PHP Code:
    $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.
    Last edited by jamestave; 07-13-2011 at 07:55 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    The query is simple enough:
    Code:
    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
    Code:
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •