MySQL inserts into multiple tables (relational)

Question

tbl_product Name | Creator | UID | Salerank tbl_price Supplier | Price | UID 

I want to insert a product, and then insert several prices into a separate table. How can we ensure that both tables have the same UID, ideally an auto-increment field? I will use PHP along with MySQL.

Thanks,

J

Answer 1

Make UID a auto_increment primary key in the product table, but only the regular primary key in the price table (without auto_increment). After you insert the itnto products, use the PHP mysql_insert_id() command. This will give the identifier generated from the last request, which will be your UID generated in the product table. Assign it to a variable and use it in the insert statement in the price table.

http://php.net/manual/en/function.mysql-insert-id.php

Answer 2

Use the GUID for the UID or, better, insert your products and insert prices, using, for example, the name of the product (if unique) to find the appropriate UID of the product.