Problem in syntax from mysql to php syntax

[newphpcoder]

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

[smoseley]

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.

[newphpcoder]

I'm trying to do is to get the value from dipping, which I get it using mysql query but when I input it in php it did not work

 

Thank you...

[newphpcoder]

The attach files is the data list I need to get, and I get it using the mysql query but I got an error when I apply it in my php code

 

 

Thank you

 

 

[newphpcoder]

I attach the sample logic documents for better understanding of my problem

 

 

Thank you

18374_.zip

[smoseley]

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.

[Barand]

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