PHP math help on $ POST array

[LinusIT]

I've described the problem as best as I can in the title, hopefully anyway.

I've got a form which can have dynamic number of rows, there are 3 input boxes for each row. When the form is submitted it firstly inserts the form date into the table "invoice" and then inserts the data from the 3 input boxes into the table "invoice_items" along with the last id from the table "invoice". This all works swimmingly, here's the code I've got to do this:

if(isset($_POST['submit'])){
$sql2 = "INSERT INTO invoice (inv_date, inv_subtotal, inv_vat, inv_total) VALUES ('$_POST[date]', '', '', '')";
$result2 = mysql_query($sql2) or die(mysql_error());
$lastid=mysql_insert_id();

foreach($_POST['grade'] as $key => $grade) {
$weight = $_POST['weight'][$key];
$price = $_POST['price'][$key];
$sql1 = "INSERT INTO invoice_items (item_inv_id, item_grade, item_weight, item_price) VALUES ('$lastid', '$grade', '$weight', '$price')";
$result1 = mysql_query($sql1) or die(mysql_error());
}
}

In the table "invoice" there are three more columns > inv_subtotal, inv_vat and inv_total which I need to populate based on the contents of the table "invoice_items" of that particular ID.

Something along the lines of selecting all the rows from table "invoice_items" where item_inv_id = $lastid. Then a foreach statement that does the following:

$item_subtotal = $row['price'] / 1000 * $row['weight'];

One last thing to do would to be sum up the total of $item_subtotal if at all possible.

Hopefully I've explained this ok, it all seems simple enough in my head but I just don't know where to start with it.

[enigma1]

you need a separate identifier column for your invoice and invoice_items tables. So if there was an invoice_id column you could reference it, pull the associated item records, calculate the totals and update the invoice table.

The item_inv_id is to distinguish items not the invoices from what I understand from the code.

[LinusIT]

Thanks for the input, I have solved it now.

item_inv_id is the identifier column to set whatever is in invoice_items against the one line in the table invoice.

I'll post the code as it may help people in future.

if(isset($_POST['submit'])){
$sql2 = "INSERT INTO invoice (inv_date, inv_subtotal, inv_vat, inv_total) VALUES ('$_POST[date]', '', '', '')";
$result2 = mysql_query($sql2) or die(mysql_error());
$id=mysql_insert_id();
}

foreach($_POST['grade'] as $key => $grade) {
$weight = $_POST['weight'][$key];
$price = $_POST['price'][$key];
$subtotal = $price / 1000 * $weight;
$sql1 = "INSERT INTO invoice_items (item_inv_id, item_grade, item_weight, item_price, item_subtotal) VALUES ('$id', '$grade', '$weight', '$price', '$subtotal')";
$result1 = mysql_query($sql1) or die(mysql_error());
}

$sql3="SELECT SUM(item_subtotal) AS subtotal FROM invoice_items WHERE item_inv_id=$id";
$result3 = mysql_query($sql3) or die(mysql_error());
while($row3=mysql_fetch_array($result3)) {
$inv_subtotal = $row3['subtotal'];
$inv_vat = $inv_subtotal * 0.2;
$inv_total = $inv_subtotal + $inv_vat;
}

$sql4 = "UPDATE invoice SET inv_subtotal='$inv_subtotal', inv_vat='$inv_vat', inv_total='$inv_total' WHERE inv_id=$id LIMIT 1";
$result4 = mysql_query($sql4) or die(mysql_error());