[PHPwestoz] [OT] Relational SQL Databases

[Timothy White]

Nicolas Connault wrote:

>
>> Ok. I wasn't sure how to do relational stuff. As Evan said I don't need
>> innoDB to do this.
>> What would be the INSERT command that would insert the ID with out
>> needing a separate SELECT? Basically I don't want to do 2 SELECTs to get
>> the Artist and Album ID's for my INSERT for each song.
>> Along that note how can I do INSERT if not exists?
>>
> TIm,
>
> I don't quite understand what you mean. To insert a new song, you
> first need to know which album it will belong to, and that will
> automatically link it to the right artist. You can either do this by
> hand as you populate your MySQL table, but that is a bit awkward, or
> you can create a little interface that shows you all available albums
> in a <select> menu. You can then pickup the ID number associated with
> that select element, and create your Insert statement from that :
> INSERT INTO Songs (`Song`,`AlbumID`) VALUES ('$song', '$AlbumID')
>
> If the album is a new one, not yet in the database, you'll have to
> create it the same way you created your song, but link it to an Artist
> instead. If the artist is also new, you'll need to start by creating
> the artist.
>
> I hope that answers your question..

Kinda.
I get the Artist info and Album info automatically with a script.
So I want to be able to minimise the number of querys.
So in PHP I have the variables $artist, $album, $song, $file
I then want to do a 'INSERT INTO Artists SET Name=$artist' 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.  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.

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?

Thanks

Tim


--
Tim White - Use the Fox, Luke!
PGP/GPG id: 602E944D, Pub Key Serv: subkeys.pgp.net
Fingerprint: 04C2 9682 B7B2 3006 009D  A9F3 067E EDCD 602E 944D
Hi! I'm a .signature virus! Copy me into your ~/.signature to help me spread!
--

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
Url : http://lists.linux.org.au/pipermail/phpwestoz/attachments/20050222/e011a5fa/attachment.pgp