Pulling tracklists from album db

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

Re: Pulling tracklists from album db

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






--

Re: Pulling tracklists from album db

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

Re: Pulling tracklists from album db

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($r esult1)):

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


$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 "

Re: Pulling tracklists from album db

Andreas Edin wrote:
> 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($r esult1)):
>
> echo "Album: $album
Title: $atitle > />Artist: $artist
Label: $alabel
"
>
>
> $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 "

Re: Pulling tracklists from album db

> Jim Hernandez wrote:
>
> I'm having a little trouble setting up the tables so they'll
> communicate with each other. I'm using Navicat. I've got the two
> tables set up, on the Tracklist table I have album id, tracknumber
> and trackname.
>
> albumid on Table 2 is a foreignkey i have set linked to the albumid
> (primary key) on Table 1. The foreignkey is then linked to albumid
> on table 2 as a primary key as well. When I try to add another
> record for album id=1 I recieve an error saying that more than one
> entry is being put in for albumid 1 and it won't take the change.
>
> Did I set up the tracklist table incorrectly?
>
> Thanks so much for all your help by the way.

It seems to me that you have set the albumid as an uniqe field in
table2. If so you wouldn't be able to add more than one record i table2
with id number 1. Here is an example how you can create your tables
with primary keys, foreign keys and some index to speed up the query:


CREATE TABLE `table1` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`album` VARCHAR(64) NOT NULL COMMENT 'Album',
`title` VARCHAR(128) NOT NULL COMMENT 'Title',
`artist` VARCHAR(64) NOT NULL COMMENT 'Artist',
`label` VARCHAR(64) NOT NULL COMMENT 'Label',
INDEX `Index_2`(`album`),
PRIMARY KEY(`id`)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1
COMMENT = 'AlbumTable';


CREATE TABLE `table2` (
`id` INTEGER UNSIGNED NOT NULL COMMENT 'Id field correspondent to
table1',
`tracknumber` INTEGER UNSIGNED NOT NULL COMMENT 'Order of track
within the album',
`trackname` VARCHAR(255) NOT NULL COMMENT 'Name of the track',
INDEX `Index_1`(`id`, `tracknumber`),
CONSTRAINT `FK_table2_1` FOREIGN KEY `FK_table2_1` (`id`)
REFERENCES `table1` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = MYISAM
AUTO_INCREMENT = 1
COMMENT = 'MetaData for albums'


Feel free to ask more questions Jim. I'll do my best to answer them.
Best regards Andreas Edin, Sweden.

--