I am creating a page which looks up information about a selected product from a table which contains item templates (pricebook) and then inserts X copies of that item into the items table then creates a relationship between each of those items and the selected purchase order record. The relationships are stored in a seperate table. The problem is that since the id's for each item are generated when they are added to the table and none of the other fields are unique I have no way to then accurately retrieve the item id so that I can add it to the relationship table.
Can anyone think of a way to accomplish this or perhaps a different way of going about it?
Thanks
CODE
include("connect.php"); //Open database connection
if(isset($_POST['submit'])){ //Check for form submission
//Lookup item info in pricebook
$sql = "SELECT * FROM inv_pricebook WHERE name = ".$_POST['item'];
$result = mysql_query($sql) or
die("Pricebook Query Failed");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
//Lookup purchase order ID
$po_id_sql = "SELECT id FROM inv_purchaseorders WHERE name = '".$_POST['po_number']."'";
$po_id_result = mysql_query($po_id_sql) or
die("There was a problem retrieving the purchase order id");
$po_id_row = mysql_fetch_row($po_id_result);
//Insert items into database
for($i = 0, $i < $_POST['qty'], $i++){
$insert_sql = "INSERT INTO inv_items (name, description, part_number, retail_price, item_cost, warranty_period)
VALUES ('".$row['name']."', '".$row['description']."', '".$row['part_number']."', '".$row['retail_price']."',
'".$row['item_cost']."', '".$row['warranty_period']."')";
mysql_query($insert_sql) or
die("There was a problem adding items");
//Insert Item ID into relationship table
//?????
//Insert Purchase Order ID into relationship table
$sql4 = "INSERT INTO inv_purchasrs_inv_items_c (inv_purchaseorders_ida, inv_purchaseorders_idb)
VALUES ('".$po_id_row[0]."', '".$')
}
}
27 Jun, 2008 - 06:20 AM
|