Inserting data into more than one table

arozzi

I have 2 tables Order and Order_items.
When ordering an item I want to have the same order_nr in both tables and it is an automatic update, to keep the order_nr unique.

Is there a good way to make sure that one of the insert statements only executes if the other executes as well.
Is it possible to use some kind of batch update or lock the tables..??

Any good suggestions????

TheIceman5

well if a query dont work then it returns a 0 or an error, and if it does work then it will return something other than zero.

But why put 2 lots of same data into 2 tables? aint that pretty redundant? database is used to eliminate redundancy, not create it. therehas to be a way to reference the record without having 2 of them if you have a good database structure.

arozzi

I have 2 tables that store different data.

Order(Order_ID, Customer_ID, Date)

Order_Item(Order_ID, Prod_ID, colour, size, quantity)

One Order have(hopefully) several Order_Items

so a normal way could be

Order(007, 123, 2003-06-08)
Order_Item(007, A12, red, large, 4)
Order_Item(007, C32, blue, medium, 2)
Order_Item(007, S12, green, small, 1)

I just need to make sure that the Order_Item will be the same in both tables. And it will be if noone manages to insert sth at the same time, that's why I need to either lock it or make sure the both queries are executed or none of them....or maybe an even better way..??

tsinka

Hi,

I hope i got the point ... Order_ID has to be the same in both tables ?

I think (with Mysql, you didn't say what database you use) the easiest way would be to change the Order_ID in Order to an auto_increment field. The ID's would then be generated automatically and incremented automatically. If you insert something into Order with mysql_query you can get that automatically generated ID by calling mysql_insert_id() after the mysql_query. You can then use the returned value to store the items in the second table.

Example (shortened):

mysql_query("INSERT INTO Order (Customer_ID,Date) VALUES ($theCustomerID,$theDate)") or die("Error: ".mysql_error());
$orderID = mysql_insert_id();
mysql_query("INSERT INTO Order_Item VALUES($orderID,...) or die ("Error: ".mysql_query());

You'll then make sure that the Order ID's are the same. If the first or the second query fails the script will die. You can replace the die calls with a custom error handling function as well.

Hope I got the point 🙂