Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: 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
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

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