[PHPwestoz] [OT] Relational SQL Databases

Timothy White <weirdo@tigris.org> · Tue Feb 22 15:46:02 2005

Once again. Another MySQL question that is related to a PHP project. I
have tried googling but can't find anything simple.
Basically I need to have a table of songs and I want to keep the artists
in another table as well as albums.
So it will look a bit like this.
Music.Artists
ID   |   Artist
1    |   Avril
2    |   Baxtar
3    |   Zebra
...
Music.Albums
ID   |   Album   | Artist
1    |   Pink    | Baxtar
2    |   Mouse   | Baxtar
3    |   Rock    | Zebra
Music.Songs
(Same as before)
CREATE TABLE library (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Filename TEXT NOT NULL,
Artist TEXT NULL,
Title TEXT NULL,
Album TEXT NULL,
FID VARCHAR(40) NOT NULL,
PRIMARY KEY ID (ID),
INDEX UID (FID(40))
) TYPE = innoDB COMMENT ='';
Now I know that I need them to be of type innoDB for relations to work.
How do I work with a database like this.
e.g. How do insert a new song and if the Artist isn't in the Artist
table it adds it and if the album isn't in the Album table it adds it as
well.
Also how do the lookups work?
e.g. If I do 'SELECT * from Songs WHERE Artist="Avril"' then how does it
return the Album field? As a ID to the Album in the Album table or as
the Album name?
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!
--
signature.asc