Jump to content

Help with query


techker

Recommended Posts

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");
 
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.