Help with query

[techker]

hey guys i have a complex code that i would like to automate..

 

So i modified an invoice program(simple invoices) for Quebec Taxes.

 

added 2 columns in database si_invoice_items tax1 and tax1

 

so in that database there is a field called unit_price

 

so what i did is set up a query that calculates the taxes on that column unit_price and inserts in calumn tax1 TPS and tax2 TVQ

 

so that part is fine but the only way i seen to do it is to make a table and echo the database si_invoice_items and put a button (convert to QC tax)so when you press it executes the querry:

 

but my issue is is that im running the query from a specific id is there a way to make the query go true all the rows and convert?then i can set up a CRON job to excute it every hour or so..

 

 

 

 

TAX1=5.00;
$TAX2 =9.975;
 
 
$id=$_GET['id'];
 
 
$q1 = "SELECT SUM(unit_price) AS TotalPrice FROM `si_invoice_items` where `invoice_id`= $id";
$res1 = mysql_query($q1);
$row3 = mysql_fetch_assoc($res1);
 
$TotalItems=$row3['TotalPrice'];
 
 
$subtotal = ($TotalItems * $TAX1/100) ;
$subtotal2 = ($TotalItems * $TAX2/100) ;
$price3 =  $TotalItems + $subtotal + $subtotal2;
$total= round($price3, 2);
 
$taxt1=round($subtotal, 2);
$taxt2=round($subtotal2, 2);
 
$TotalItems2=round($TotalItems, 2);
 
 
mysql_query("UPDATE `si_invoice_items` SET `tax1` = $taxt1, `tax2` = $taxt2 WHERE `invoice_id` = $id");
 

[techker]

ok i think i got it.

 

 

$q1 = "UPDATE si_invoice_items SET tax1 =(SELECT ROUND(SUM(unit_price* $TAX1/100),2))";
$res1 = mysql_query($q1);
$row3 = mysql_fetch_assoc($res1);
 
 
$q2 = "UPDATE si_invoice_items SET tax2 = (SELECT ROUND(SUM(unit_price* $TAX2/100),2))";
$res2 = mysql_query($q2);
$row2 = mysql_fetch_assoc($res2);