SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    PHP Code:
    $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:

    PHP Code:
    /----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.
    Attached Files Attached Files

  2. #2
    Non-Member bronze trophy
    Join Date
    Nov 2009
    Location
    Keene, NH
    Posts
    3,760
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    $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.

    Code:
    	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.

    Code:
    	$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:
    Code:
    			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.

  3. #3
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here i get the $Comp

    PHP Code:
     $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

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by deathshadow60 View Post
    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:
    Code:
    $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.

    Code:
    	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.

    Code:
    	$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:
    Code:
    			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.
    Thank you for your suggestion


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •