Calculating tax correctly using Mysql and php -

i trying re-calculate tax on sale after entered database. can calculate information correctly before storing in database, calculating database reporting purposes has proved troublesome due rounding errors.

the application displays interface allows 1 add items , view subtotal, taxes , totals.

example input:

item 1: 9.95

item 2: 2.95

subtotal: 12.90

taxes (could different each item, same each item)

2.900%: 0.37

1.000%: 0.13

1.100%: 0.14

3.000%: 0.39

tax total: 1.03

total: 13.93

the way calculate taxes before storing database:

foreach item $price {   foreach tax $percent   {     $taxes[$percent]+=$price*($percent/100);   } }  foreach($taxes $percent => $tax_amount) {   $taxes[$percent] = round_to_2_decimals($tax_amount); } 

after calculating information store information in database can retrieved later reporting purposes.

example schema:

phppos_sales_items - sale_id - item_id - price  phppos_sales_items_taxes - sale_id - item_id - tax_percent 

sample data phppos_sales_items table

sale_id: 1, item_id: 1, item_unit_price: 9.95

sale_id: 1, item_id: 2, item_unit_price: 2.95

sample data phppos_sales_items_taxes table

sale_id: 1, item_id: 1, percent: 2.90

sale_id: 1, item_id: 1, percent: 1.00

sale_id: 1, item_id: 1, percent: 1.10

sale_id: 1, item_id: 1, percent: 3.00

sale_id: 1, item_id: 2, percent: 2.90

sale_id: 1, item_id: 2, percent: 1.00

sale_id: 1, item_id: 2, percent: 1.10

sale_id: 1, item_id: 2, percent: 3.00

sample query calculate tax sale

(select sum(tax) (select round((item_unit_price)*(sum(percent)/100),2) tax phppos_sales_items inner join phppos_sales_items_taxes using (sale_id, item_id) sale_id = 1 group sale_id, item_id) tax) 

output 1.04 when need 1.03. query works in cases, fails lot when there rounding problems.

i need able generate summary informatation accuratly using sql. open suggestions on how calculate information before sale , in database.

ok, so, question, understand has not programming.

so, have item worth $1. now, have 2 taxes on it, 1 1.4% , 1 1.3%. now, doing

tax1 = round($1*0.014, 2) = $0.01 tax2 = round($1*0.013, 2) = $0.01 totaltax = $0.02 

so far good... ok, let's other way. if add taxes, should pay total of 1.4 + 1.3 = 2.7%, means:

totaltax = round($1*0.027, 2) = $0.03 

see happened here?

now, of these ways right? if physically need pay tax1 separately tax2, each of them quantified in cents , first way right. but, feel pay final total tax, add percents first, calculate tax , pay nearest cent.

---

in relation code, should 1.03, that's right, query doing mistake similar first variant rounding result each item before adding them (it doing round(price1*tax1, 2) + round(price2*tax2, 2)). (actually, complete, have 3 places round: individual tax level, item level , on total level.)

to round @ end instead of each item, move out round:

(select round(sum(tax),2) (select (item_unit_price)*(sum(percent)/100) tax phppos_sales_items inner join phppos_sales_items_taxes using (sale_id, item_id) sale_id = 1 group sale_id, item_id) tax)