0
tbl_product
Name | Creator | UID | Salerank

tbl_price
Supplier | Price | UID

I want to insert a product and then insert multiple prices into a seperate table. How is it possible to ensure that both tables had the same UID ideally an auto increment field? I will be using PHP alongside MySQL.

Thanks,

J

2 Answers2

5

Make UID an auto_increment primary key on the products table, but just a regular primary key on the prices table (no auto_increment). After you insert itnto products, use the PHP command mysql_insert_id(). This will get the ID generated from the last query, which will be your UID generated on the products table. Assign it a variable and use it in your insert statement on the prices table.

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

Community
  • 1
  • 1
desertwebdesigns
  • 1,035
  • 8
  • 14
  • +1: There's no harm in creating a separate primary key for the prices table; you could use a composite key (more than one column) instead too. – OMG Ponies Jul 12 '10 at 00:31
  • 4
    Remember to make tbl_price.UID reference tbl_product.UID as a foreign key, and to use InnoDB. Foreign key checks are your friend! – Charles Jul 12 '10 at 00:37
  • @OMG, right. I wasn't saying don't use a primary key on the prices table, just not auto_increment @Charles, Agreed. +1 – desertwebdesigns Jul 12 '10 at 01:18
0

Use a GUID for the UID, or better, insert your products and the insert the prices using e.g. the name of the product (assuming unique) to look up the relevant product UID.

Will A
  • 24,780
  • 5
  • 50
  • 61
  • Using an artificial/surrogate key for the foreign key relationship is a better idea because the name can change, requires unique names, etc. Relate on descriptive text at your peril... – OMG Ponies Jul 12 '10 at 00:29