I'm having a hard time figuring how to link database rows in a PHP / MySql project. My order submission script currently splits information and stores it into 2 tables.
The first one is called "Orders" and contains:
$OrderId, $CustomerName, $CustomerEmail, $OrderTotal, $OrderTaxes
//and other infos about the ORDER
The second one is called "Items" and contains all the BOUGHT products infos:
$ProductId, $OrderedQty
//for each one and such...
It has to be this way because the "Items" table will be searched by different "departments" who will only be shown the parts of the orders they are responsible for. But they all have to get the "Orders" infos for shipping purposes.
Knowing that the "OrderId" column is a primary key generated on the "Orders" table itself, and that my INSERT TO commands are both executed at the same time, how can I link an "Order Id" column in both tables ?
Do I have to generate some random key to match them ?
If I were to use a foreign key, how would the database know which product goes with which order since they are submited at the same time ?
Or is it fast enough to INSERT in "Orders" -> SELECT $OrderID -> INSERT in "Items" ?
How does one usually do this ? Can't figure this one out.
Thanks in advance for your precious help!