Need help in condition and logic to get the last row data

Hi…

I have code in getting the lotcodes and output_qty from dipping table until the req_qty will be equal on the total output_qty from lotcodes.

this codes work correctly on the first:


$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, 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) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
 $pcode = $row['pcode'];
 $LOT_CODE = $row['code'];
 $DATE_ENTRY = $row['DATE_ENTRY'];
 $qty = $row['qty'];
 $cumulative = $row['cumulative'];


 $sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
 $res_in = mysql_query($sql, $con);
}

but when I approve or create another jo number, I notice that the list of lotcode from the first jo displayed also on the second jo.

The correct is if on the last lotcode has a balance it will be the first lotcode displayed on the second jo.

here is my code now:


/----check if jo_dump has data-----
$sql = "SELECT * FROM jo_dump";
$res_jodump = mysql_query($sql, $con);
$row_jodump = mysql_num_rows($res_jodump);

//----if jo_dump is <=0, get the lotcode, qty , etc from dipping table until the sum of total output will equal to req qty.
if ($row_jodump <= 0){
$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, 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) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
 $pcode = $row['pcode'];
 $LOT_CODE = $row['code'];
 $DATE_ENTRY = $row['DATE_ENTRY'];
 $qty = $row['qty'];
 $cumulative = $row['cumulative'];


 $sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
 $res_in = mysql_query($sql, $con);
}
}

//---else select the last lotcode, qty, etc if the last lotcode has a balance output so it would be the first lotcode on the second jo, here I got a problem in logic and coding to get the last lotcode and display it on the second approve jo number.
else{
    $sql = "SELECT jo_number, pcode, lotcode, qty, cumulative, date_entry FROM jo_dump ORDER BY date_entry DESC LIMIT 0,1";
    $res_jp = mysql_query($sql, $con);

    $row_jp = mysql_fetch_assoc($res_jp);

    $lotcode = $row_jp['lotcode'];
    $qty = $row_jp['qty'];


$cmd = "set @t = 0";
$result = mysql_query($cmd);
$cmd2 = "set @rqty=$ReqQty";
$res = mysql_query($cmd2);
$sql = "SELECT SUBSTR(LOT_CODE, 9,4) as pcode, 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) = '$Comp' AND (@t < @rqty)";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
 $pcode = $row['pcode'];
 $LOT_CODE = $row['code'];
 $DATE_ENTRY = $row['DATE_ENTRY'];
 $qty = $row['qty'];
 $cumulative = $row['cumulative'];


 $sql = "INSERT INTO jo_dump (jo_number, pcode, lotcode, qty, cumulative, date_entry) VALUES ('$currentSR', '$pcode', '$LOT_CODE', '$qty', '$cumulative', '$DATE_ENTRY')";
 $res_in = mysql_query($sql, $con);
}
}

I attach my sample documents for better understanding
Thank you so much.

Ouch… that code is hard to follow and I think you’ve completely overthought the solutions to a lot of your problems… some tips:

  1. avoid needlessly vague/cryptic names on things.

  2. use more than single space indentation.

  3. if you are only using the result of a function once, don’t waste time AND memory creating a variable for it.

  4. don’t waste time inside the loop making copies of values that already exist!!!

Like this:


$pcode = $row['pcode'];
$LOT_CODE = $row['code'];
$DATE_ENTRY = $row['DATE_ENTRY'];
$qty = $row['qty'];
$cumulative = $row['cumulative'];

Total waste of execution time and memory allocation inside the loop.


	mysql_query("
		INSERT INTO jo_dump
		(
			jo_number,
			pcode,
			lotcode,
			qty,
			cumulative,
			date_entry
		) VALUES (
			'$currentSR',
			'$row[pcode]',
			'$row[LOT_CODE]',
			'$row[qty]',
			'$row[cumulative]',
			'$row[DATE_ENTRY]'
		)",
		$con
	);

Much cleaner and easier to follow, and would execute a LOT faster using less memory.

You’re also fetching the entire data set just to count them – are you actually using that elsewhere? if not, you’re wasting memory and slow as molasses.

“SELECT * FROM jo_dump” – Given what you’re doing with that, that should probably be a “SELECT COUNT(*) FROM jo_dump” – unless later on you’re actually using that result set.

You are also sending multiple server requests where one would do the job rather handily.


	$result=mysql_query("
		SET
			@t = 0;
		SET
			@rqty=$reqQty;
		SELECT
			SUBSTR(LOT_CODE, 9,4) as pcode,
			LOT_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) = '$Comp' AND (@t < @rqty)
	");

Though I think a lot of your issue may be here. That is a needlessly complex query that I’m not even sure I follow the point of – you’re doing a lot of stuff SQL side that just increases the size of the result set.

I’d really have to see the full data structures and understand the reasoning behind these queries to weigh in more as to why it’s not pulling up what you’re expecting – since I’m not quite grasping what you’re expecting this to do. Each item in the list has it’s quantity overridden if it’s less than the sum of all items already processed?!? What’s that supposed to do?

Seriously:


			CASE
				WHEN @t+OUTPUT_QTY > $reqQty
				THEN $reqQty - @t
				ELSE OUTPUT_QTY
			END as qty,
			@t := @t + d.OUTPUT_QTY as cumulative

Not sure what you’re even trying to accomplish there… Thought rewriting it to lose the unnecessary parts would make it clearer, it didn’t.

deathshadow60 gave you a lot of good tips. Re-organize your code as suggested might help you to spot the errors.

You did mention ReqQty = QtyReq * Qty but where did you get the value for $Comp?

here i get the $Comp


 $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'];

Thank you

Thank you for your suggestion