I need to run a select statement to get data from a table column lineitems.quantity, sum the values where the id is the same and save the result in a column in a second table. My problem is that the data type for lineitems.quantity is varchar but I need to run calculations on the data and save them in invoices.totalquantity (data type int). Each value in lineitems.quantity is an integer but is derived from user-input text fields that I believe always go through as a string value?
As for where I implement the code, I either need to select the data from MySQL using php, convert the types, run my calculations and update the invoices table or I need to set up a trigger directly in MySQL so the columns get selected and the conversion, calculation and update all happen after each new lineitems row is inserted.
NOTE: the invoices table and the lineitems table both get their inserts/updates after the same button is clicked in the application so they both update at the same time, (though, lineitems only gets updated if a change occurred to that part of the page, where as invoices always gets updated when the button is clicked).
My Code:
Currently, some code I have on the application side is as follows:
$sglitotalsget="SELECT (CONVERT(lineitems.quantity) as INT)as sgtotqty FROM lineitems INNER JOIN invoices on lineitems.invoiceid=invoices.id WHERE lineitems.invoiceid = ".$this->invoiceid."";
$this->db->query($sglitotalsget);
while($this->db->fetchArray($sgtotalsresult)){
$sglitotalsupdate = "UPDATE invoices SET invoices.totalquantity = ".array_sum($sglitotalsget)." WHERE invoices.id= ".$this->invoiceid."";
}
I have also attempted to set up a trigger but it always failed. That code is as follows:
NOTE: the trigger was always set up on lineitems after insert/update.
BEGIN
SET @totalqty = (SELECT SUM(lineitems.quantity) as qty FROM lineitems INNER JOIN invoices ON lineitems.invoiceid=invoices.id);
INSERT INTO invoices SET invoices.NEW.totalquantity = @totalqty;
END
AND:
BEGIN
update invoices INNER JOIN lineitems on (invoices.id=lineitems.invoiceid) SET invoices.totalquantity=SUM(lineitems.quantity) WHERE invoices.id=lineitems.invoiceid;
END
I'd still prefer to do this via the trigger method if possible.
Thank you!!