SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Difficult query (at least to a newbie)

    What I'm trying to do is write a query that will display the songs associated with an album. Here's how I set up the tables in my database for reference:

    Table: album
    album_id
    album_name
    artist_id

    Table: artist
    artist_id
    artist_name

    Table: song
    song_id
    song_name
    artist_id

    Table: songs_to_albums
    sta_id
    song_id
    album_id

    Here's what I've written so far:
    PHP Code:
    $sql mysql_query("SELECT album_id, album_name FROM album WHERE album_id = '" $album_id "'"); 
    I don't know how to go about including the song information. Can someone please show me?

    Any help you can provide would be greatly appreciated.

    Thank you.

  2. #2
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In my newbie mind...you would need four seperate queries to get the information from those other tables. You would then need to use mysql_feth_row... to grab the row that you would like in the other queries...

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     $sql mysql_query("SELECT* FROM album WHERE album_id = '$album_id'"); 
    $row mysql_fetch_row($sql);

    $song_sql mysql_query("SELECT* FROM song WHERE artist_id = '$row[artist_id]'"); 
    Something like that.

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response but I'm pretty sure it should just be query (the person that actually helped me set up the table structure actually wrote the query but I can't find it ). Anyone else?

  5. #5
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you are looking for is a sql query which involves a table join. Check this article out.

    HTH.

  6. #6
    SitePoint Guru ujjwal's Avatar
    Join Date
    Oct 2004
    Location
    kolkata
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select s.song_name,a.album_name,ar.artist_name from song s,album a,songs_albums sa,artist ar where s.song_id=sa.song_id and a.album_id=sa.album_id and s.artist_id=ar.artist_id and ar.artist_id=a.artist_id
    It will show all u can give * instead of my selction and pull all the values and check out
    -::Ujjwal::-
    Visit My Site

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ujjwal
    Code:
    select s.song_name,a.album_name,ar.artist_name from song s,album a,songs_albums sa,artist ar where s.song_id=sa.song_id and a.album_id=sa.album_id and s.artist_id=ar.artist_id and ar.artist_id=a.artist_id
    It will show all u can give * instead of my selction and pull all the values and check out
    Thanks for the response. However, I couldn't get that query to work. Any more suggestions?

  8. #8
    SitePoint Guru ujjwal's Avatar
    Join Date
    Oct 2004
    Location
    kolkata
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am not tested u give * instead of u selct fields and there is 4 table so 4 conditions also there..If u not iunderstood pls read Joining(inner joining)
    -::Ujjwal::-
    Visit My Site

  9. #9
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Confusion
    Thanks for the response. However, I couldn't get that query to work. Any more suggestions?
    I think ujjwal was just giving you the shorhand version of what to do, like a model. Those aren't your table names. Just used to illustrate his point.

    I am just reading about joins, so I don't know if this will work, but have tried to apply what ujjwal has said in a query statement:


    PHP Code:
    <?php
    $sql 
    "SELECT song.song_name, album.album_name, artist.artist_name
            FROM song,album,artist
            WHERE song.song_id=songs_to_albums.song_id
            AND album.album_id=songs_to_albums.album_id
            AND song.artist_id=artist.artist_id
            AND artist.artist_id=a.artist_id"
    ;
    ?>
    ujjwal is right. You may benefit from reading that article that I first suggested as a general building block for this 'architectural' query...

    -kind regards

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When your queries get long like that you will appreciate your column naming convention.
    Table: album
    album_id
    album_name
    artist_ref

    Table: artist
    artist_id
    artist_name

    Table: song
    song_id
    song_name
    artist_ref

    Table: songs_to_albums
    sta_id
    song_ref
    album_ref
    Others may disagree, but try this before going thru any more documentation. Take that last table as an example:

    Table: songs_to_albums
    sta_id
    song_id
    album_id

    Thats exactly right - - but if you rename the references to foreign keys as "_ref" your life will get a bit easier:

    Table: songs_to_albums
    sta_id
    song_ref <- thats a reference to the song_id
    album_ref <- thats a reference to the album_id

    What you want to end up with is as many unique, but meaningful names as you can get - (its not always possible).

    Now, when you make your sql statements in your various joins, you are doing
    " ..where song_id=song_ref ...."
    and not all this
    " ..where songs.song_id=songs_to_albums.songs_id ...."

    1) it makes it easier to read (debug)
    2) it reduces sql errors

    I indicated in the quote which ones seem to be _ref (erences) to foreign keys (unique IDs in other tables). HTH
    Last edited by paulyG; Oct 14, 2005 at 09:23. Reason: looks misleading

  11. #11
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    /* off topic

    Cool. I am just learning all this, too.

    Thanks paulyG

    */

  12. #12
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the responses...This is still all VERY confusing to me. Can someone tell me if I'm on the right track?
    PHP Code:
    <?
    $album_id 
    $_GET['alid'];
    ?>
                            <?

    // Perform MySQL query on only the current page number's results
    $sql "SELECT song.song_name, album.album_name, artist.artist_name
            FROM song,album,artist
            WHERE song.song_id=songs_to_albums.song_id
            AND album.album_id=songs_to_albums.album_id
            AND song.artist_id=artist.artist_id
            AND artist.artist_id=a.artist_id"
    ;

    // Get rows from database (hint: fetch)
    $i 0;
    while(
    $row mysql_fetch_assoc($sql)){
        
    // Build your formatted results here.
       
    $album_id $row['album_id'];
       
    $album_name $row['album_name'];
       
    $artist_id $row['artist_id'];
       
    $artist_name $row['artist_name'];
       
    $song_id $row['song_id'];
       
    $song_name $row['song_name'];
       echo 
    '<h2>' $album_name '</h2>' .
       
    '<p>' $song_name '</p>';
    }
    ?>

  13. #13
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Confusion
    PHP Code:
    <?
    $album_id 
    $_GET['alid'];
    ?>
                            <?

    // Perform MySQL query on only the current page number's results
    $sql "SELECT song.song_name, album.album_name, artist.artist_name
            FROM song,album,artist
            WHERE song.song_id=songs_to_albums.song_id
            AND album.album_id=songs_to_albums.album_id
            AND song.artist_id=artist.artist_id
            AND artist.artist_id=a.artist_id"
    ;

    ?>
    Why dont you concentrate on just getting the SQL query correct first? get the answer you want in PhpMyAdmin or whatever you use to manage your database. Try and focus on one problem at a time.

    You have not asked you query to include the important where statement?

    WHERE album_id=$album_id

    Now, when you are testing your SQL statement (like I said above) use a REAL number first on your test data.

    I havent looked at the validity of your sql, just to tell you the above.

    What I'm trying to do is write a query that will display the songs associated with an album. Here's how I set up the tables in my database for reference:
    Thats not true, you are trying to get the artist too.

    Listen, try and get just the songs first.

  14. #14
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind...again...
    Last edited by Confusion; Oct 16, 2005 at 10:03.

  15. #15
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you figured this out? If so, can you post your code?? I am curious and would like to resolve this lesson in my mind. *thanks*

  16. #16
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rodney H.
    Have you figured this out? If so, can you post your code?? I am curious and would like to resolve this lesson in my mind. *thanks*
    This works (even though I'm sure some of that is useless and/or redundant):
    PHP Code:
    SELECT song.song_namealbum.album_nameartist.artist_nameartist.artist_idsong.song_idalbum.album_idsta_id
            FROM song
    albumartistsongs_to_albums
            WHERE song
    .artist_id=artist.artist_id
            
    AND song.song_id=songs_to_albums.song_id
            
    AND album.album_id='$album_id'
            
    AND album.album_id=songs_to_albums.album_id 
    I'll try to clean it up later .

    Thanks for the help everyone.

  17. #17
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool. Very nice. Thank you.

    And, if it is not too much trouble, can you show me the while loop that you use to echo out the values? That is where I am stuck in my own example (?).

    Much thanks.

  18. #18
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To figure out exactly what you DONT need from your sql try this:

    print("<pre>");
    print($row); <--this would be the variable that holds each row

    @rodneyH

    Post the bit where you're stuck

  19. #19
    SitePoint Wizard Mike Borozdin's Avatar
    Join Date
    Oct 2002
    Location
    Edinburgh, UK
    Posts
    1,743
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just wondering why on earth you need songs_to_albums table, you can have album_id in your songs table.

  20. #20
    SitePoint Evangelist Rodney H.'s Avatar
    Join Date
    Sep 2005
    Location
    Chicago, IL
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    paulyG:

    Thanks, man! that helped me figure it out. I used print_r and saw the values were there. Then, it just took a second to convert the array keys to variable names, and VOILA!!

    Multi grazie!



  21. #21
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mike Borozdin
    Just wondering why on earth you need songs_to_albums table, you can have album_id in your songs table.
    Often times in the hip-hop genre, songs appear on multiple albums. I couldn't think of another way to set it up .


Bookmarks

Posting Permissions

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