Re: [PHP-DB] multiple tables insert
Barry, Hey, you're digging into it and making progress. You will end up with what you want, although it will seem like a long process. So you are welcome - Miles Thompson At 03:21 PM 1/12/2002 +1300, Barry Rumsey wrote: I had been looking for a pre made script for a lyric site like phpnuke but the only one I could find was myphplyrics which is quite hopeless ,so I've had no choice but to try and make my own any way I would like to thank you for all your help so far. B.J.Rumsey. - Original Message - From: Miles Thompson [EMAIL PROTECTED] To: Barry Rumsey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 12:51 PM Subject: Re: [PHP-DB] multiple tables insert 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 '-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
Re: [PHP-DB] multiple tables insert
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 '-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]
Re: [PHP-DB] multiple tables insert
I had been looking for a pre made script for a lyric site like phpnuke but the only one I could find was myphplyrics which is quite hopeless ,so I've had no choice but to try and make my own any way I would like to thank you for all your help so far. B.J.Rumsey. - Original Message - From: Miles Thompson [EMAIL PROTECTED] To: Barry Rumsey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 12:51 PM Subject: Re: [PHP-DB] multiple tables insert 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 '-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
