How to store selected songs ids in db?

[tony007]

Hi all i have a db for my music collection. It has one table with the following feilds on it:

[B]ID[/B]|filename|artist|album|title|track|comments|genre|seconds|filesize|bitrate

I already created pages that with checkboxes that user select indvidual songs and by click of play button it goes and plays those songs for me.

Now i created another button next to play that it supposed to write the selected songs to db and save the ids of slected songs.Note: i use song ids to pull indvidual songs from db to be played by my song player!

My problem is i do not how to design a few tables that keep track of selected songs for each user along with the name of playlist for each user!! In processs i do not want to modify my current table i just want to add more tables to help me achive what i want. I be happy if an expert tell me how i can create tables that store play list(selected songs)info for each user and later i be able to query them easily in order to pull out playlist of each user.Thanks

 

[FoFa]

First you should setup a user table, and when you go into your application ask which user it is. I don'[t think you need to delve into the security aspects, just asking should be suffiecent for this. Once you know the user you can store the playlist. Now the best way would be two table, one to store the playlist and user information, and one to store the list of songs. So your playlist table would have userID, playlistID (a primary key) and the playlist name (playlist name is something a user uses to identify it). Then a songlist table that would have the PlaylistID as a foregin key and the songID.

At least that is my opinion.

 

[tony007]

First you should setup a user table, and when you go into your application ask which user it is. I don'[t think you need to delve into the security aspects, just asking should be suffiecent for this. Once you know the user you can store the playlist. Now the best way would be two table, one to store the playlist and user information, and one to store the list of songs. So your playlist table would have userID, playlistID (a primary key) and the playlist name (playlist name is something a user uses to identify it). Then a songlist table that would have the PlaylistID as a foregin key and the songID.

At least that is my opinion.

Many thanks to u reply.so u mean i need to alter :

ID|filename|artist|album|title|track|comments|genre|seconds|filesize|bitrate ?
How i can set up PlaylistID as a foregin key and the songID?

could u tell wirte me the create table statments for those tables with their relationships?Thanks

 

[FoFa]

User Table
[B]ID[/B]|username|ect.

Playlist Table
[B]ID[/B]|UserID|PlaylistName

PlayListItems table
[B]ID[/B]|PlaylistID|SongID

So basically a playlist belongs to a user (foreign Key Userid) and a user can have one or many playlists.
The PLayListItems table ties a SONG (SongID foreign key) back to the song table and to a playlist (PlaylistID foreign key)