php/mysql help me please

[halo-eleven]

Hello
I'm making discography section for my site using php and mysql.
here is my db structure:
I have 3 tables- albums, tracks and albums_tracks

albums table holds information about albums, album name, release date...ect
tracks table is for song information, song name, lyrics..
albums_tracks is table that connects albums and tracks table.
EX:

albums_tracks table

albums_ID - track_ID
1-13
2-14
5-12
ect...

but I'm running into a problem with double disc albums. I would like to make something like this:

track list of cd 1
track 1
track 2

track list of cd2.
track 1
track 2

instead of

track listing
track1
track2
track1
track2

what I'm trying to say is that, I want page to return track listing for each CD separately.
maybe someone can help me with this...

thanks a lot
any kind of help will be apprediated.

[zawadi]

try this:
[code]
$a_t_sql = "select  albums_ID , track_ID from albums_tracks";
$a_t_query = mysql_query($a_t_sql);
$a_t_num= mysql_num_rows($a_t_sql);
for($n=0;$n<$a_t_num;$n++){
    $a_t_col = mysql_fetch_array($a_t_query);
    $a_sql = "select * from albums where albums_id = '".$a_t_col[0]."'";
    $t_sql = "select * from track where track_id = '".$a_t_col[1]."'";
    $a_query = mysql_query($a_sql);
    $a_col = mysql_fetch_array($a_query);
    echo $a_col[album_name];
    $t_query = mysql_query($t_sql);
    $t_num= mysql_num_rows($t_sql);
    for($nt=0;$nt<$t_num;$nt++;){
        $t_col = mysql_fetch_array($t_query);
        echo $t_col[track_name];
    }
}
[/code]

[halo-eleven]

Thanks, but i think at first i need to change my database structure or add some tables... :(

[wickning1]

Your structure is fine. Zawadi's code is a little weird. Here's how I would write it:

[code]$albums= mysql_query("select * from albums ORDER BY albums_ID");

while ($album = mysql_fetch_assoc($albums)) {
    echo 'Album: ' . $album['album_name'];

    $tracks = mysql_query("select t.* FROM tracks t INNER JOIN albums_tracks a ON t.track_ID=a.track_ID WHERE a.albums_ID='$album[albums_ID]'");

    while ($track = mysql_fetch_assoc($tracks)) {
        echo '     ' . $track['track_name'];
    }
}[/code]

[txmedic03]

Remove my reply, please.