|
|
Posted by Jim Hernandez on 11/06/05 08:15
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($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
>
>
>
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.
Navigation:
[Reply to this message]
|