Adding totals of values given in dynamic rows populated from database

Question

I have a dynamic table, where Column A is populated dynamically from the database and one column is an input fields for ratings. The code is in the link below :

Click here to view the code

PHP Code

<?PHP 
    $myquery=" select ROW_NUMBER,Weightage FROM Appraisal_Objectives WHERE Serial_Number like '%1152' ORDER BY Row_Number asc";
    $fetched=sqlsrv_query($conn,$myquery) ; 
    if( $fetched === false ) { die( print_r( sqlsrv_errors(), true ));}
    while($res=sqlsrv_fetch_array($fetched,SQLSRV_FETCH_ASSOC))
    {
        $Weightage=$res['Weightage'];
        $Row_Number=$res['ROW_NUMBER'];                                 
        if($Weightage=='==')
        {
            echo "<tr><td><b>Sub-Total</td></b>";
            echo "<td><b><input type='number' class='form-control' value='70'></td></td></b>";
            echo "<td><input type='number' name='SubTotals[]' id='SubTotals' class='SubTotals form-control' readonly onchange='calculateGrandTotal(this);'></td></tr>";         
        }
        else
        {
            echo "<tr><td> </td>";
            echo "<td>".$Weightage."</td>";
            echo "<td><input type='number' name='Rating[]' id='Rating' class='Rating form-control' onkeypress='return isNumberKey(event)' onChange='calc(this.parentElement.parentElement);calculateSubTotal(this);'></td>";
            echo "<td style='display:none'><input type='number' name='max_rating[]' id='max_rating' class='max_rating form-control' onChange='calc(this.parentElement.parentElement));' value='$Weightage' td>";
            echo "<td style='display:none'><input type='number' name='Row_Number[]' id='Row_Number' class='Row_Number form-control' value='$Row_Number' td></tr>";
        }
    }
?>

What I am trying to do :

All the data elements should add up and give the sub-total in the Sub-Total field present on top of the fields. The sub-totals should then add up and and give a grand total. I've attached a photo for better understanding.

My issue :

When I am inputing values instead of going to their respective subtotals, they add up all together. Where have I gone wrong? Appreciate any suggestions..

NB : The fields are dynamic, and can change the row number depending on the database values. Calculation needs to be done depending on the dynamic fields and NOT the static fields that I have provided in the code.

`Click here to view the code` seems broken. can you fix? or provide in jsfiddle. — JF it, Mar 16 '16 at 12:27
@JFit It is broken because, it has a php code attached to it where it gets data from the database. Would yet be the same if I added it to jsfiddle — SR1092, Mar 16 '16 at 12:31
can you perhaps remove that and the unwanted stuff, you gotta provide a more condensed version of what you're after.. like, you say you want sums right? stick in some dummy values. - a real db connection isnt required. — JF it, Mar 16 '16 at 12:33
I could do that, but the values are dynamic when generated from the database.. Won't making them static values change the query? — SR1092, Mar 16 '16 at 12:33
add that in the answer - that this has to work regardless of how many of the white text boxes are available. should be easy enough as we can add a class to them on return from db etc. — JF it, Mar 16 '16 at 12:38

score 0 · Answer 1 · answered Mar 16 '16 at 15:08

Sorry for the delay. http://plnkr.co/edit/MbHyTefvZ9kHENJmNcVi?p=preview

Although close, I didn't like how you were doing that.. Consider a solution like this - it works by just seeking through until it hits something - so will treat dynamic stuff the way you wanted it to:

$(document).ready(function(){
    function updateTotals(ctrl){
      var total = parseInt(ctrl.val());

      ctrl.parents('tr').prevAll("tr").each(function(ind,val){
        if($(val).find('.SubTotals').length > 0)
          return false;
        else
        {
          var num = $.trim($(val).find('.Rating').val())== "" ? 0 : $(this).find('.Rating').val();;
          total += parseInt(num);
        }
      });
      ctrl.parents('tr').nextAll("tr").each(function(ind,val){
        if($(val).find('.SubTotals').length > 0)
          return false;
        else
        {
          var num = $.trim($(val).find('.Rating').val())== "" ? 0 : $(this).find('.Rating').val();;
          total += parseInt(num);
        }
      });
      ctrl.findNext('.SubTotals').val(total);


    var gTotal = 0;
    $('.Rating').each(function() {
        gTotal += Number($(this).val());
    });
    $('#Grand_Total').val(gTotal);
    }

      $('#tb input').on( "change", function(){
        updateTotals($(this));
      });

    });

    (function( $ ){
   $.fn.findNext = function(sel) {
     var $result = $(sel).first();
     if ($result.length <= 0) {
       return $result;
     }
     $result = [];
     var thisIndex = $('*').index($(this));
     var selIndex = Number.MAX_VALUE;
     $(sel).each(function(i,val){
       var valIndex = $('*').index($(val));
       if (thisIndex < valIndex && valIndex < selIndex) {
         selIndex = valIndex;
         $result = $(val);
       }
     });
     return $result;
   }; 
  })( jQuery );

I removed the events from your input boxes - also, you had duplicate ID's everywhere.. Try to avoid that.

Thanks for the answer! :) Appreciate the time spent.. I left the duplicate ID's as it is because those fields are dynamic (in the php code). Secondly, everything is working fine but the sub-totals are not being added to the respective ones, instead being added to the one of the next slot. — SR1092, Mar 17 '16 at 10:09

Adding totals of values given in dynamic rows populated from database

1 Answers1