Hi…
I create mysql syntax for query testing before i input to my php code
here is my mysql code:
set @t = 0;
set @rqty=31968;
SELECT LOT_CODE as code, DATE_ENTRY,
CASE WHEN @t+OUTPUT_QTY > @rqty
THEN @rqty -@t
ELSE OUTPUT_QTY
END as qty,
@t := @t + d.OUTPUT_QTY as cumulative
FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' AND (@t < @rqty);
and i attach the sample output of the above query.
Now that query test is work i will input that code to my php codes.
$sql = "SELECT SKUCode, Materials, Comp, Qty
FROM bom WHERE SKUCode = '$SKUCode'";
$res = mysql_query($sql, $con);
($row = mysql_fetch_assoc($res));
$Materials = $row['Materials'];
$Qty = $row['Qty'];
$Comp = $row['Comp']; //P28
//-----Compute Req Qty and Save to table---//
$ReqQty = $Qty * $POReq; // 31968
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);
$t = 0;
$sql = "SELECT LOT_CODE as code, DATE_ENTRY,
CASE WHEN $t+OUTPUT_QTY > $ReqQty
THEN $ReqQty -$t
ELSE OUTPUT_QTY
END as qty,
$t := $t + d.OUTPUT_QTY as cumulative
FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND ($t < $ReqQty)";
when I echo the query:
I got this:
SELECT LOT_CODE as code, DATE_ENTRY, CASE WHEN 0+OUTPUT_QTY > 31968 THEN 31968 -0 ELSE OUTPUT_QTY END as qty, 0 := 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = ‘P28’ AND (0 < 31968)
then I run it to the sql
and I got an error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = ‘’ at line 1
(0 ms taken)
Any help is highly appreciated
Thank you so much
