homepage Welcome to WebmasterWorld Guest from 54.234.2.94
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP math help on $ POST array
LinusIT




msg:4432025
 10:49 am on Mar 22, 2012 (gmt 0)

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




msg:4432517
 11:58 am on Mar 23, 2012 (gmt 0)

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




msg:4432633
 4:37 pm on Mar 23, 2012 (gmt 0)

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());

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved