Do maths on several dynamic rows

Adamhumbug · July 19, 2019

HI All,

I am wanting to do some maths with JS on my website that is built dynamically from information held in my database. It is going to be a very simple multiplication but i am struggling to know how to write the JS to know which line to run on.

The code that builds it is the following:

while ($row = mysqli_fetch_array($result)){
									echo "<tr>";
									echo "<td style='width:70%;'>".$row['money_item']."</td>";
									echo "<input type='hidden' name='moneyId[]' amount='".$row['money_value']."' value='".$row['money_id']."'>";
									echo "<td class='text-center'><input class='Qty up text-center' value='".$row['money_qty']."' name='moneyQty[]'  type='text'></td>";
									echo "<td><span id='moneyTotal'>This</span></td>";
									echo "</tr>";
								}

and it looks like this when built:

<tbody>
   <tr class="text-center">
      <th>Item</th>
      <th value="0">Quantity</th>
      <th>Line Value</th>
   </tr>
   <tr>
      <td style="width:70%;">90.00 Float Bag</td>
      <input type="hidden" name="moneyId[]" amount="90.00" value="1">
      <td class="text-center"><input class="Qty up text-center" value="100" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
   <tr>
      <td style="width:70%;">1000.00 Change Bag</td>
      <input type="hidden" name="moneyId[]" amount="1000.00" value="4">
      <td class="text-center"><input class="Qty up text-center" value="" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
   <tr>
      <td style="width:70%;">150.00 Float Bag</td>
      <input type="hidden" name="moneyId[]" amount="150.00" value="2">
      <td class="text-center"><input class="Qty up text-center" value="" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
   <tr>
      <td style="width:70%;">300.00 Change Bag</td>
      <input type="hidden" name="moneyId[]" amount="300.00" value="3">
      <td class="text-center"><input class="Qty up text-center" value="" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
   <tr>
      <td style="width:70%;">First Aid Kit</td>
      <input type="hidden" name="moneyId[]" amount="0.00" value="5">
      <td class="text-center"><input class="Qty up text-center" value="" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
   <tr>
      <td style="width:70%;">Waiter Pad</td>
      <input type="hidden" name="moneyId[]" amount="0.00" value="6">
      <td class="text-center"><input class="Qty up text-center" value="" name="moneyQty[]" type="text"></td>
      <td><span id="moneyTotal">This</span></td>
   </tr>
</tbody>

I am wanting to multiply the amount by the amount (which in the first row is 90.00) by the qty which is an input box. I can work out how to do the standard maths if there was only one row but i dont know how to get this to run on each line and update the money total.

Help as always is appreciated.

Kind Regards

Adam

requinix · July 19, 2019

Forget multiple rows for a minute. Can you do it for just one row?

Barand · July 19, 2019

A couple of comments

Why do you want to mutiply the amount by the amount?
If you are sending those amounts to your server, ignore them. Requery your database for the price values based on the product ids (which you should be sending along with the quantities. (If you use prices sent by the user you could end up selling everything for 0.01). By all means show the total values, but purely for customer convenience.

Adamhumbug · July 19, 2019

1 hour ago, Barand said:

A couple of comments

Why do you want to mutiply the amount by the amount?

If you are sending those amounts to your server, ignore them. Requery your database for the price values based on the product ids (which you should be sending along with the quantities. (If you use prices sent by the user you could end up selling everything for 0.01). By all means show the total values, but purely for customer convenience.

Sorry, i repeated myself there. I wanted to multiply the amount by the qty and show this value on the screen. This will have no impact on the values that are stored in the database but will just be used on screen to show the user the total amount.

Adamhumbug · July 19, 2019

I have made some changes.

while ($row = mysqli_fetch_array($result)){
									echo "<tr>";
									echo "<td style='width:70%;'>".$row['money_item']."</td>";
									echo "<input id='moneyId".$i."' type='hidden' name='moneyId[]' data-amount='".$row['money_value']."' value='".$row['money_id']."'>";
									echo "<td class='text-center'><input id='moneyquantity".$i."' class='Qty up text-center' value='".$row['money_qty']."' name='moneyQty[]'  type='text'></td>";
									echo "<td><span id='moneyTotal".$i."'></span></td>";
									echo "</tr>";
									$i++;
								}


	<input data-amount="90.00" id="moneyId1" name="moneyId[]" type="hidden" value="1"><input data-amount="0.00" id="moneyId2" name="moneyId[]" type="hidden" value="5"><input data-amount="0.00" id="moneyId3" name="moneyId[]" type="hidden" value="6"><input data-amount="1000.00" id="moneyId4" name="moneyId[]" type="hidden" value="4"><input data-amount="150.00" id="moneyId5" name="moneyId[]" type="hidden" value="2"><input data-amount="300.00" id="moneyId6" name="moneyId[]" type="hidden" value="3">
	<table>
		<tr class="text-center">
			<th>Item</th>
			<th>Quantity</th>
			<th>Line Value</th>
		</tr>
		<tr>
			<td style="width:70%;">£90 Float Bag</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity1" name="moneyQty[]" type="text" value="3"></td>
			<td><span id="moneyTotal1">£270</span></td>
		</tr>
		<tr>
			<td style="width:70%;">First Aid Kit</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity2" name="moneyQty[]" type="text" value=""></td>
			<td><span id="moneyTotal2"></span></td>
		</tr>
		<tr>
			<td style="width:70%;">Waiter Pad</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity3" name="moneyQty[]" type="text" value=""></td>
			<td><span id="moneyTotal3"></span></td>
		</tr>
		<tr>
			<td style="width:70%;">£1000 Change Bag</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity4" name="moneyQty[]" type="text" value=""></td>
			<td><span id="moneyTotal4"></span></td>
		</tr>
		<tr>
			<td style="width:70%;">£150 Float Bag</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity5" name="moneyQty[]" type="text" value=""></td>
			<td><span id="moneyTotal5"></span></td>
		</tr>
		<tr>
			<td style="width:70%;">£300 Change Bag</td>
			<td class="text-center"><input class="Qty up text-center" id="moneyquantity6" name="moneyQty[]" type="text" value=""></td>
			<td><span id="moneyTotal6"></span></td>
		</tr>
	</table>

var qty = document.getElementById("moneyquantity1").value;
	var amount = document.getElementById("moneyId1").getAttribute("data-amount");
	var total ='£'+ qty * amount;
	$("#moneyTotal1").append(total);

i have not added the handler yet to make it update live but it does what i want for one line.

Edited July 19, 2019 by requinix
I've moved it for you

Barand · July 19, 2019

I would use class names instead id names. It simplifies iteration.

I would also use the money_id as the index in the input fieldname for the qty.

$result = [
             ['money_id' => 1, 'money_item' =>'Item A', 'money_value' => 300,  'money_qty' => 10  ],
             ['money_id' => 2, 'money_item' =>'Item B', 'money_value' => 500,  'money_qty' => 10  ],
             ['money_id' => 3, 'money_item' =>'Item C', 'money_value' => 1000, 'money_qty' => 10  ],
             ['money_id' => 4, 'money_item' =>'Item D', 'money_value' => 1500, 'money_qty' => 10  ],
             ['money_id' => 5, 'money_item' =>'Item E', 'money_value' => 2000, 'money_qty' => 10  ]
          ];
          
echo "<table>";
foreach ($result as $row){
    $mid = $row['money_id'];
    echo "<tr>";
    echo "<td style='width:70%;'>".$row['money_item'];
    echo "<input type='hidden' class='moneyValue' value='{$row['money_value']}' data-id='$mid' ></td>";
    echo "<td class='text-center'><input class='Qty up text-center' value='{$row['money_qty']}' name='moneyQty[$mid]'  type='text' data-id='$mid' ></td>";
    echo "<td><span class='moneyTotal' data-id='$mid' >Total</span></td>";
    echo "</tr>\n";
}
echo "<tr><td colspan='2'>Total</td><td id='grandtotal'>Grand Total</td></tr>\n";
echo "</table>\n"

When you process the POSTed data you can then just

foreach ($_POST['moneyQty'] as $mid => $moneyQty) {
    // now you have the id to retrieve price from database
    // as you process each quantity
}

Javascript

<script type="text/javascript">
    $().ready( function() {
        
        calcLineTotals()
        
        $(".Qty").change( function() {
            calcLineTotals()
        })
    })
    
    function calcLineTotals()
    {
        var gtot = 0
        $(".Qty").each( function(k,v) {                                    // foreach element of class "Qty"
            var mid = $(v).data("id")                                      //     get its data-id
            var qty = $(v).val()                                           //     get the qty 
            var price = $(".moneyValue[data-id="+mid+"]").val()            //     get price from moneyValue element with same data-id
            $(".moneyTotal[data-id="+mid+"]").html(qty * price)            //     put total in total element with same data-id
            gtot += qty* price                                             //     accumulate grand total
        })
        $("#grandtotal").html(gtot)                                        //  put grand total in element with id = grandtotal
    }
</script>

Adamhumbug · July 24, 2019

On 7/19/2019 at 9:12 PM, Barand said:

I would use class names instead id names. It simplifies iteration.

I would also use the money_id as the index in the input fieldname for the qty.


$result = [
             ['money_id' => 1, 'money_item' =>'Item A', 'money_value' => 300,  'money_qty' => 10  ],
             ['money_id' => 2, 'money_item' =>'Item B', 'money_value' => 500,  'money_qty' => 10  ],
             ['money_id' => 3, 'money_item' =>'Item C', 'money_value' => 1000, 'money_qty' => 10  ],
             ['money_id' => 4, 'money_item' =>'Item D', 'money_value' => 1500, 'money_qty' => 10  ],
             ['money_id' => 5, 'money_item' =>'Item E', 'money_value' => 2000, 'money_qty' => 10  ]
          ];
          
echo "<table>";
foreach ($result as $row){
    $mid = $row['money_id'];
    echo "<tr>";
    echo "<td style='width:70%;'>".$row['money_item'];
    echo "<input type='hidden' class='moneyValue' value='{$row['money_value']}' data-id='$mid' ></td>";
    echo "<td class='text-center'><input class='Qty up text-center' value='{$row['money_qty']}' name='moneyQty[$mid]'  type='text' data-id='$mid' ></td>";
    echo "<td><span class='moneyTotal' data-id='$mid' >Total</span></td>";
    echo "</tr>\n";
}
echo "<tr><td colspan='2'>Total</td><td id='grandtotal'>Grand Total</td></tr>\n";
echo "</table>\n"

When you process the POSTed data you can then just


foreach ($_POST['moneyQty'] as $mid => $moneyQty) {
    // now you have the id to retrieve price from database
    // as you process each quantity
}

Javascript


<script type="text/javascript">
    $().ready( function() {
        
        calcLineTotals()
        
        $(".Qty").change( function() {
            calcLineTotals()
        })
    })
    
    function calcLineTotals()
    {
        var gtot = 0
        $(".Qty").each( function(k,v) {                                    // foreach element of class "Qty"
            var mid = $(v).data("id")                                      //     get its data-id
            var qty = $(v).val()                                           //     get the qty 
            var price = $(".moneyValue[data-id="+mid+"]").val()            //     get price from moneyValue element with same data-id
            $(".moneyTotal[data-id="+mid+"]").html(qty * price)            //     put total in total element with same data-id
            gtot += qty* price                                             //     accumulate grand total
        })
        $("#grandtotal").html(gtot)                                        //  put grand total in element with id = grandtotal
    }
</script>

This is perfect, thank you so much - the explanations were great as well so thanks again!!

Adamhumbug · July 24, 2019

I do have one further question - i did stray a little from exactly what you did and i think you may have addressed what i am about to ask in your middle block of code but i didnt quite get it.

when posting, i am getting undefined variable.

while ($row = mysqli_fetch_array($result)){
									$moneyid = $row['money_id'];
									echo "<tr>";
									echo "<td style='width:70%;'>".$row['money_item'];
									echo "<input type='hidden' class='moneyValue' value=".$row['money_value']." data-id='".$moneyid."' name='moneyId[]'></td>";
									echo "<td class='text-center'><input class='Qty up text-center' value='".$row['money_qty']."' name='moneyQty".$moneyid."'  type='text' data-id='".$moneyid."'></td>";
									echo "<td><span class='moneyTotal' data-id='".$moneyid."'></span></td>";
									echo "</tr>";
								}
								echo "<tr><td colspan='2'>Total</td><td id='grandTotal'>Grand Total</td></tr>";

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $jobId = $_SESSION['current_job_id'];
    if(isset($_POST['moneyQty'])){$qty = $_POST['moneyQty'];}

    // prepare insert query
    $stmt = $conn->prepare("INSERT INTO ssm_money_order (money_qty, job_id, money_id) 
                        VALUES (?,?,?) 
                        ON DUPLICATE KEY UPDATE 
                            money_qty = VALUES(money_qty)"
                        ); 
    
    
    foreach ($_POST['moneyId'] as $k => $mid) {

        if ($qty[$k] > 0) {
            $stmt->bind_param("iii", $qty[$k], $jobId, $mid);
            $stmt->execute();
        }
    } 
}

The above is the php on the show page and the sql on the update request.

I am getting undefined variable on:

if(isset($_POST['moneyQty'])){$qty = $_POST['moneyQty'];}

Which i understand as i have changed the name of the input from

name='moneyQty[]'

to

name='moneyQty".$moneyid."'

but i am not sure if i just change the foreach that i have to what you said or if there is more changes to be made.

To be honest i dont 100% understand that for each loop

Edited July 24, 2019 by Adamhumbug

Barand · July 24, 2019

Consider this simple bit of form code

<form method="POST">
    <?php
         for ($id=1; $id<=3; $id++) {
             echo "<input type='text' name='aqty$id' value='0'>";
             echo "<input type='text' name='bqty[$id]' value='0'><br>";
         }
         echo "<input type='submit' name='btnSubmit' value='Submit'>";
    ?>
</form>

The inputs "aqty"have the id concatenated to end of the name (as you have done) ie aty1, aqty2, aqty3

The "bqty" fields have names I had them "bqty[$id]".

When the form is posted, the post data array looks like this

Array
(
    [aqty1] => 10
    [aqty2] => 20
    [aqty3] => 30
    [bqty] => Array
        (
            [1] => 10
            [2] => 20
            [3] => 30
        )

    [btnSubmit] => Submit
)

The "aqty" items are posted as 3 separate variables ($_POST['aqty1'] ...$_POST['aqty3']) .

The "bqty" items are posted in an array where the keys are the id values. This enables me to use the

foreach ($_POST['bqty'] as $id => $qty)

to loop through all the posted values

EDIT: In my example the ids are seqential so you can process your method with a simple for() loop. However, in the real world where the data has come from a query, the ids are effectively random so that method cannot be used ( EG ids are 13, 17, 129 ). My method would work with all id values.

Edited July 24, 2019 by Barand

Adamhumbug · July 25, 2019

Thank you this really helped.

Sign In

Do maths on several dynamic rows

Recommended Posts

Adamhumbug

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Adamhumbug

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information