php database

[dillonit]

I want to have a site that list song and playlist. attached is the database that i have designed.

Is there a better way to configure this database?

 

Each playlist will have 10 songs.

 

Thanks ahead,

Adam

 

[attachment deleted by admin]

[Mchl]

Yes there is.

You can have variable length playlists by adding one more table

 

table playlists_songs
playlist_id,song_id
     1           1
     1           2
     1           3
     2           1
     2           4
     2           5

etc...

 

 

 

[dillonit]

How would i query and display the playlist with this joiner table?

[Mchl]

You'd need one query to display playlist data

SELECT playlist_name FROM playlistTable WHERE playlist_id = 1

 

And another to get items on this playlist

 

SELECT song_name, song_link FROM songTable AS s, playlists_songs AS ps WHERE ps.song_ID = s.song_ID AND ps.playlistID = 1

[dillonit]

Do i need to set a primary key in the table_playlist_songs table?

[Mchl]

Not an auto_increment PK for sure. Create primary key on both columns of this table.

[dillonit]

Ok sorry for all the questions.

 

If there are going to be multiple numbers that are the same, how can i put a primary key on them.

 

I have one playlist already listed in the playlist_song_table. And the songs and playlist in there respected tables as well.

1,1

1,2

1,3

1,4

1,5.....

1,17

 

 

I tried to add the primary key after the data was already in there. I am using phpmyadmin to do this. Do i need to set the primary keys first then add the data?

[Mchl]

1. Delete primary key for that table if you have one.

2. Display the table in 'structure' view.

3. Select both rows with checkboxes.

4. Under a table with rows, should be an icon for primary key, click it.

 

You should now have primary key that's based on two columns.

 

Mind, that it means, you one song can't occur more than once within one playlist