PDA

View Full Version : Problem in syntax from mysql to php syntax



newphpbees
15 May 2012, 10:11 PM
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);

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