Jump to content

problem in syntax from mysql to php syntax


newphpcoder
 Share

Recommended Posts

Hi..I create mysql syntax for query testing before i input to my php codehere is my mysql code:

set @t = 0;set @rqty=31968;SELECT LOT_CODE as code,  DATE_ENTRY,  CASE WHEN @t+OUTPUT_QTY > @rqtyTHEN @rqty  [email="-@t"]-@t[/email] ELSE OUTPUT_QTYEND 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 > $ReqQtyTHEN $ReqQty  -$t ELSE OUTPUT_QTYEND 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 sqland I got an error:Error Code : 1064You 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 appreciatedThank you so much

  • Like 2
Link to comment
Share on other sites

0 := 0 + d.OUTPUT_QTY That's not valid, the := is used to set variables in SQL. The SQL variables have the @ sign before them. You replaced the SQL variables with scalar values by using PHP variables so now the query has numbers instead of variables. It's not going to work like that, you should keep the SQL variables in the query. If you want to set their values you do that in the first few lines of the query like you see in the original SQL.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...