Barry, Good question, and you have discovered one of MySQL's limitations, it does not enforce functional constraints. To put it another way, you can design parent-child relationships, but the db will never say "You can't add this because the parent record doesn't exist". So you have to do it in code, not a big deal,and it's been done that way for years with other flat file, pseudo-relational database -- FoxPro, dBase, etc.
You work around it by capturing the auto_increment value using the mysql_insert_id() function. Hence you can insert the values for the artist table, call mysql_insert_id() and assign it to a variable, say $art_id_key, and use it in the insert for the album table. Then repeat, calling mysql_insert_id() and assigning it to $album_id_key after the insert into the album table so that you will have it for the insert into the tracks table. And so forth. You will be doing all these inserts on the same thread, so if someone else inserts into the same table you should be safe, according to the documentation. If A inserts into artist, B inserts into artist, then A calls mysql_insert_id() the value returned is for A's insert, not B's. MySQL has a separate thread for each of A and B. Now, don't trust me. Read up on the last_insert_id() and mysql_insert_id() functions in both the MySQL and PHP docs. I'll toss this out - what if you decide to capture other information, such as the name of the producer. Can your design handle that? What changes might you have to make to accommodate the change? Regards - Miles Thompson At 10:17 AM 1/12/2002 +1300, Barry Rumsey wrote: > Thanks for that. >But it has brought up another question. I'll list the tables so you'll know >what i'm on about. >TABLE 1 : Album >alb_id int(11) NOT NULL auto_increment, > art_id int(11) NOT NULL default '0', > alb_name varchar(255) NOT NULL default '', > alb_image varchar(255) NOT NULL default '', > alb_year date NOT NULL default '0000-00-00', > alb_genre varchar(100) NOT NULL default '', > PRIMARY KEY (alb_id) > >TABLE 2 : Artist >art_id int(11) NOT NULL auto_increment, > art_name varchar(255) NOT NULL default '', > art_details mediumtext NOT NULL, > PRIMARY KEY (art_id) > >TABLE 3 : Songs >song_id int(11) NOT NULL auto_increment, > song_name varchar(255) NOT NULL default '', > song_lyrics mediumtext NOT NULL, > song_info mediumtext NOT NULL, > PRIMARY KEY (song_id) > >TABLE 4 : Tracks >track_id int(11) NOT NULL auto_increment, > alb_id int(11) NOT NULL default '0', > art_id int(11) NOT NULL default '0', > song_id int(11) NOT NULL default '0', > PRIMARY KEY (track_id) > >Ok I'll be able to work out the simple part of the inserts, but what about >the auto_increment parts thatt relie on the information in the other files. >eg: 'artist.art_id' should be the same as 'album.art_id' the problem is >that 'artist.art_id' is auto_increment and 'album.art_id' is not. So if I >added an artist in the artist table and the auto_increment gave it the value >of 5, how would I update the 'album.art_id' with the same value? > > > > > > > > > > > > > >----- Original Message ----- >From: "Miles Thompson" <[EMAIL PROTECTED]> >To: "Barry Rumsey" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> >Sent: Saturday, January 12, 2002 9:50 AM >Subject: Re: [PHP-DB] multiple tables insert > > > > Barry > > > > See below ... > > > > At 09:09 AM 1/12/2002 +1300, Barry Rumsey wrote: > > >Two questions: > > >1) If I have a url in the db that points to a image , how do I get php to > > >get this image and display it ? > > > > Check the <IMG> tag in an HTML reference > > > > > > >2) I have 4 tables in the db and would like to know of a good tutorial on > > >inserting to multiple tables form a single form. > > > > There are many tutorials, the one I most frequently recommend is by Julie > > Meloni at http://www.thickbook.com pick the one on custom error messages > > as she develops it very nicely. > > > > As for the inserts, in the "processing" part of the script, you'll know > > what I mean when you examine the tutorial, execute an INSERT for the data > > you want to insert in each of the tables. INSERT acts on one table at a > > time, so you'll have four of them. > > > > That should get you going - Miles Thompson > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
