[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [PHPwestoz] [OT] Relational SQL Databases

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