Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Problem In Syntax From Mysql To Php Syntax

mysql syntax

  • Please log in to reply
1 reply to this topic

#1 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 15 May 2012 - 07:20 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  [email="-@t"]-@t[/email] 
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

Attached Thumbnails

  • sample data query.JPG

  • 0

#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 16 May 2012 - 01:08 AM

i might bet that your sintax error is this :

Explained

You are using an aliased table ( dipping d ), that means that you should prefix d. any call to dipping table in the sql statement and you are doing this just once in : d.OUTPUT_QTY
the solution is either aliasing all the calls, or erasing the the alias an its only call
and in the other hand you have either extra characters in the case when or they lack a parenthesis,
and "0" can't be a name of a variable ( what you are using as cumulative, you should give it a name, i think what you meant was @t )

i will paint the wrongs :

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)


how should be coded :

option 1: no alias

SELECT LOT_CODE as code, DATE_ENTRY,
CASE WHEN
(0+OUTPUT_QTY) > 31968 THEN (31968 -0) ELSE OUTPUT_QTY END as qty, @t := @t + OUTPUT_QTY as cumulative FROM dipping WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' AND (0 < 31968)




option 2: all aliased
SELECT d.LOT_CODE as code, d.DATE_ENTRY,
CASE WHEN
(0+d.OUTPUT_QTY) > 31968 THEN (31968 -0) ELSE d.OUTPUT_QTY END as qty, @t := @t + OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(d.LOT_CODE, 9,4) = 'P28' AND (0 < 31968)


I am not 100% sure that @t accumulator will do the trick, but if it does ... good for you :D ,

now that i see it again, i thing that -0 and +0 you got, should be the @t, right ?
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download