dtnmnw3697
dtnmnw3697
2016-10-19 20:14

将数据类型结果从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!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答