[PHPwestoz] [OT] Relational SQL Databases

[Nicolas Connault]

> I get the Artist info and Album info automatically with a script.
> So I want to be able to minimise the number of querys.

That's fair enough if you're highly concerned about speed. Otherwise 
it's OK to have three queries in one script, if you're dealing with 
small amounts of information.

> So in PHP I have the variables $artist, $album, $song, $file
> I then want to do a 'INSERT INTO Artists SET Name=$artist' 

That's not the right syntax. It should be "INSERT INTO Artists (`Name`) 
VALUES ('$artist')"  Check the syntax for these statements in the MySQL doc.

> and I want
> some more in that SELECT that only adds it if it isn't already in there.
> Then I need to get the ID for that artist so probably another select
> unless there is a quicker way.  

Unfortunately I don't know any function in MySQL that lets you retrieve 
the auto-increment ID of a field you just created. You normally have to 
do another select, which is not hard if you do this: "SELECT ID FROM 
Artists ORDER BY ID DESC LIMIT 1" This will give you the last created 
ID, which normally would be the last record you created.

> Then a similar INSERT for the Album,
> once again I need to get the Album ID so that I can use it in the last
> INSERT which actually inserts the Song.

As above, another query. Remember that these queries will be very fast 
since you are only selecting one field and only one record, and 
searching a unique primary key.

>
> Then I also need to be able to a SELECT of the following format although
> now it won't work as the database is different.
> SELECT * FROM Songs WHERE Artist LIKE "A%"
> How would I write that one?
>
This one is easy: "SELECT Songs.* FROM Songs, Albums, Artists WHERE 
Songs.AlbumID = Album.ID AND Album.ArtistID = Artist.ID AND 
Artist.Artist LIKE 'A%' "

I would rename the Artist.Artist field to Artist.Name if I was you, it's 
never good to name a field by the same name as the table.

More helpful?

Nicolas