Jump to content


Photo

php/mysql help me please


  • Please log in to reply
4 replies to this topic

#1 halo-eleven

halo-eleven
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 07 March 2006 - 12:49 PM

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.

#2 zawadi

zawadi
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationChippenham UK

Posted 07 March 2006 - 01:00 PM

try this:
$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];
    }
}

That's the beauty of asymmetric warfare. You don't need a lot of money, or an army of people. You can do it from the comfort of your living room, with a beer in your hand.

#3 halo-eleven

halo-eleven
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 07 March 2006 - 01:48 PM

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

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 March 2006 - 02:12 PM

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

$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'];
    }
}


#5 txmedic03

txmedic03
  • Members
  • PipPipPip
  • Advanced Member
  • 313 posts
  • LocationCall, TX, USA

Posted 07 March 2006 - 04:19 PM

Remove my reply, please.

SEMPER FIDELIS!

I can't stop you from doing something silly, but at least I can help you do it right.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users