Posted by Andreas Edin on 11/05/05 23:03

Jim Hernandez wrote:

> Andreas Edin wrote:
> > Jim Hernandez wrote:
> >
> >
> > > I'm setting up a database for albums and I've decided to break the
> > > tracklist down track by track in the table, i.e., albumkey
> > > (primary) atitle
> > > aartist
> > > alabel
> > > track1
> > > track2
> > > track3
> > > track4... on through track 30.
> > > I want to call the tracks via php form the database and put them
> > > into a sidebar on a webpage. Say the db has room for 30 as mine
> > > does but the album only has 14 tracks, how do I communicate to
> > > sql to pull until the tracks are null and stop.
> > >
> > > Thanks for any help or advice
> >
> >
> > In this case the easiest way is to create two table's.
> > One with the album and the other one with the correspondent tracks.
> >
> > Example:
> > Table1:
> > id <--- this field is the uniqe field
> > album
> > atitle
> > artist
> > alabel
> >
> > Table2:
> > id ---> is the same as the album id
> > ---> but in this table it's not a uniqe value, it's an integer
> > trackname
> >
> > For each track you insert a new record in table 2.
> >
> > Then when you want to create an output från this you just makes
> > two itterations. The first one is on table1, where you can sort it
> > as you like by albumname, title or label.
> >
> > For each record in table1 you select all with the same correspondent
> > number in table2.
> >
> > I hope this gave you some hint where to start.
> > Andreas Edin, Sweden
> >
> >
> >
> >
> >
> >
> so in table 2 itd be
> id1 track 1 name
> id1 track 2 name
> id1 track 3 name
> id1 track 4 name
>
> or
>
> track 1 track 2 track 3 track 4
> id1 name name name name
> id2 name name name name


The first one is to prefer.
Here is an example:

Table1:
id album atitle artist alabel
1 Album1 Title1 Artist1 Label1
2 Album2 Title2 Artist1 Label2
3 Album3 Title3 Artist1 Label3


Table2:
id trackNumber TrackName
1 1 Name of the first track <--- this is album1
1 2 Name of the second track
1 3 Name of the trird track
1 4 Name of the fourth track
and so on...
2 1 Name of the first track <--- this is album2
2 2 Name of the second track
2 3 Name of the trird track
2 4 Name of the fourth trahe
and so on...
3 1 Name of the first track <--- this is album3
3 2 Name of the second track
3 3 Name of the trird track
3 4 Name of the fourth trahe
and so on...


Here is some code example how to get the information listed:

$sqlst1 = "Select * from table1 order by album;";
$result1 = mysql_query($sqlst1);

while
(list($id,$album,$atitle,$artist,$alabel)=mysql_fetch_row($result1)):

echo "<b>Album:</b> $album <br /><b>Title:</b> $atitle<br
/><b>Artist:</b> $artist <br /><b>Label:</b> $alabel<br />"


$sqlst1 = "Select trackNumber, TrackName from table2 where id=$id
order by trackNumber;";
$result1 = mysql_query($sqlst1);
while (list($trackNumber,$TrackName)=mysql_fetch_row($result2))
echo "<li>$trackNumber/. $TrackName<li />";
endwhile;

echo "<hr />";
endwhile;

Good Luck! If you want more information just say it and i'll try to
help you.
Andreas Edin, Sweden

[Back to original message]