Page 1 of 1

Help creating inserts to database

Posted: Mon Aug 25, 2008 9:17 pm
by ANYang
deleted

Re: Help creating inserts to database

Posted: Tue Aug 26, 2008 3:50 am
by Ziq
You have thousands errors in your PHP code. Begin this simple task like insert and select data from database. FORGET Like operator, use =. You have problem with database designing. For what tables artist/album and song/album is used? Try execute this code!

For example:
Database music:
Artists table
id_artist [int primary auto_increment]
name [varchar(200)]
// other

Albums table
id_album [int primary auto_increment]
id_artist [int]
album_name [varchar(200)]
// other

Songs table
id_song [int primary auto_increment]
id_album [int]
song [varchar(200)]
// other

Re: Help creating inserts to database

Posted: Wed Aug 27, 2008 12:47 am
by califdon
Ziq may be correct, although I didn't read through your entire script for the "thousands" of errors he reported. :wink:

IF (and it's an important IF) an album has only ONE artist, and IF a song can be on only ONE album, then you won't need those intermediate tables, just the 3 entity tables, as Ziq suggested. However, if the same song could be on several albums, for example, that's a many-to-many relationship and you DO need the intermediate table. Likewise for multiple artists relating to the same album. So, depending on what your data could be like, you may or may not need the 2 intermediate tables.

I would ask, however, how you will deal with albums where individual songs are by different artists (since your intermediate table is only between albums and artists)? Or is that not a possibility for your application?

On to your data entry question, no, you need to use separate SQL statements for each table, but they should all be in one block of code. If you are using a database that supports Transactions, you probably should enclose the Inserts within a Transaction. But remember that most of the time you won't be adding records to all 3 tables anyway. You'll enter the album (and, I suppose, the artist) just once, followed by several songs for that album. Also, if an artist (or a song) is already in the database and is present on another album, you'll need to establish links, but not new records for artist or song. So you have a considerable task of planning your data entry process to account for all this.

Re: Help creating inserts to database

Posted: Wed Aug 27, 2008 5:34 am
by Ziq
IF (and it's an important IF) an album has only ONE artist, and IF a song can be on only ONE album, then you won't need those intermediate tables, just the 3 entity tables, as Ziq suggested. However, if the same song could be on several albums, for example, that's a many-to-many relationship and you DO need the intermediate table. Likewise for multiple artists relating to the same album. So, depending on what your data could be like, you may or may not need the 2 intermediate tables.
Yeh. I'm not think about this...

Re: Help creating inserts to database

Posted: Wed Aug 27, 2008 6:02 am
by onion2k
Ziq wrote:FORGET Like operator, use =.
It's pretty clear from the script that it's searching for a partial artist title give the two wildcard operators at the start and end. LIKE is the right thing to use in that situation.