将数据类型结果从MySQL SELECT语句从varchar转换为int

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!!

douchun5976
douchun5976 如果在算术表达式中使用它们,MySQL和PHP都会自动将字符串转换为数字。当您存储回VARCHAR列时,它们会转换回字符串。
3 年多之前 回复
dongqishun6409
dongqishun6409 “lineitems.quantity中的每个值都是一个整数,但是是从用户输入的文本字段派生出来的,我相信它总是以字符串值的形式出现?”-不,这根本不是真的。您只需选择了错误的数据类型。
3 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问