Jump to content

Problem in syntax from mysql to php syntax


newphpcoder

Recommended Posts

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

post-101569-13482403514318_thumb.jpg

Link to comment
Share on other sites

You have a few things wrong with your code...

 

1. You're using PHP variables in the context of a SQL query.  Doesn't work that way.  The PHP value gets rendered statically in the context of the SQL string you're generating (thus, equates to 0).

2. You're using SQL params in your query.  Can't do that in the context of a query in PHP.

 

I suggest instead, you use a GROUP clause to achieve the results you want, but honestly, I can't tell exactly what you're trying to do... if you could explain the results you're looking for, you could probably get better assistance.

Link to comment
Share on other sites

Again, you're giving a lot of information,but explaining very little.

 

What is 'cumulative' for?  It looks like you're trying to get a sum total, or somehow limit the amount of 'dipping" you retrieve.

 

Explain what and how much data you're trying to retrieve.

Link to comment
Share on other sites

Continuing from your other post (which you should probably have done to avoid confusion) this is the php implementation

 

<?php
include("testDBconnect.php");

$rqty = 100;

mysql_query ("set @t = 0");          // reset @t sql variable

$sql = "SELECT lot_code as code, 
date_entry, @t := @t + d.output_qty as cumulative,
CASE WHEN @t+output_qty > $rqty THEN @t - $rqty ELSE output_qty END as qty
FROM dipping d
WHERE SUBSTR(lot_code,6)='P28' AND @t < $rqty";

$res = mysql_query ($sql) or die (mysql_error());

echo '<pre>';
echo "CODE     \t DATE     \t QTY \t CUMULATIVE_QTY<br />";
while (list ($code, $dt, $cum, $qty) = mysql_fetch_row($res)) {
    echo "$code \t $dt \t $qty \t $cum<br />";
}
echo '</pre>';
?>

 

results:

CODE     	 DATE     	 QTY 	 CUMULATIVE_QTY
00001P28 	 2012-05-15 	 50 	 50
00002P28 	 2012-05-16 	 25 	 75
00003P28 	 2012-05-17 	 25 	 125

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.