RE: [PHP-DB] help me on projecting some tables

1. Don't know answer to question. However, surround the INSERTs with LOCK/UNLOCK Table commands to prevent the problem you desecribe. Or, the script (PHP,PERL,C,Python...) can insert product record, retrieve the product_id, then use that for the product_id into the traits table. -----Original Message----- From: Tim Foster [mailto:tfoster@pdxinc.com] Sent: Friday, November 16, 2001 9:42 AM To: PHP DB Subject: RE: [PHP-DB] help me on projecting some tables A couple of questions: > INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Sugar"); 1. Will LAST_INSERT_ID() work reliably in a multi-user environment? What happens if you're in the middle of inserting a dozen records and someone else inserts a record? Does MySQL keep the LAST_INSERT_ID()s separate (since the web application will see both transactions as being done by the same "user")? 2. Is there any benefit to having a 3rd table to keep track of "characteristic"s ? I guess that quesiton is best answered by examining the business needs. If the characteristics are few and don't change often, it seems to me that you'd want a 3rd table to keep them. That way, the spelling is similar, etc, and therefore you can do (accurate) queries based on the characteristics (if the need ever came up) TIM -Whenever you hear a man speak of his love for his country, it is a sure sign he expects to be paid for it. > -----Original Message----- > From: Rick Emery [mailto:remery@excel.com] > CREATE TABLE products( > product_id int auto_increment primary key, > description char(50) default "", > quantity int not null, > unit enum > ("each","lb","ounce","gallon","quart"), > price decimal(5,2) not null > ) > > CREATE TABLE traits( > characteristic char(25) default "", > product_id int, > description char(25) > ) > > In the above example, when you wanted to add a characteristic for a > particular product, you simply add a record into traits and set > traits.product_id equal to products.product_id. This will make it REAL EASY > doing joins on this combo as well. For example: > > INSERT INTO products VALUES(NULL, "Coca Cola",1,"ounce",0.39); > INSERT INTO traits VALUES("Liquid",LAST_INSERT_ID(),"Seltzer-based"); > INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Sugar"); > INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Brown Dye"); > INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Caramel > coloring"); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net