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:

ID|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

[si_the_geek]

Your current table should arguably be re-designed, as you currently have duplication of data for the album field at least. However this isn't essential, as you are just using more size than you could.

In order to save playlists I would recommend 3 tables, one to save the User info, one to save Playlist info, and one to save the contents of the playlist. eg:

Users
UserID - Autonumber/Identity
UserName - String/Char/Varchar

Playlists
PlayListID - Autonumber/Identity
UserID - reference to the UserID of the 'owner' in Users
PlayListName - String/Char/Varchar

PlayListContents
PlayListContentsID - Autonumber/Identity
PlayListID - reference to the PlayListID in PlayList
SongID - reference to the ID in your current table


To find all playlists for a user, you can use SQL like this:

SELECT P.PlayListID, P.PlayListName
FROM Playlists P
INNER JOIN Users U ON (U.UserID = P.UserID)
WHERE UserName = '<user name here>'

To list the songs in a playlist, you can use SQL like this:

SELECT t.* 
FROM Playlists P
INNER JOIN PlayListContents C ON (C.PlayListID = P.PlayListID)
INNER JOIN <CurrentTableName> t ON (t.ID = C.SongID)
WHERE P.PlayListID = <PlayListID from the SQL above>