Total of numerical column in looped table (not total of query)

Thank you in advance for detailed help and responses.

I've been stumped on this for weeks. Been researching online and cannot figure this out. Please help.

It's important to note - the column I am trying to get the total of is NOT stored in my sql database. SELECT SUM is not going to work for this. I also would prefer to not write the SUBTOTAL column into sql -- because if I would need to change the hours or the rate (if there was a clerical error), I would rather just change 1 field, than 2.

Here's what my table looks like, populated from sql query: table looks like

Where the table says TOTAL, I need it to add up the column above it and populate the total.

Here's my code - it's long because I'm including everything in case you need it.

$staffquery = mysqli_query($con, "SELECT * FROM kmis_transcribers WHERE transcriber_projectid = '$projectinvoiceid ' ORDER BY transcriber_calltime ASC");

$staffcount = mysqli_num_rows($staffquery);
for($i=0; $i<$staffcount; $i++) {
$staffarray = mysqli_fetch_array($staffquery);
$staffid = $staffarray['transcriber_id'];
$staffregid = $staffarray['transcriber_registryid'];
$staffposition = $staffarray['staff_position'];
$staffcalltime = $staffarray['transcriber_calltime'];
$staffrate = $staffarray['staff_rate'];
$staffhours = $staffarray['transcriber_hours'];
$staffovertime = $staffarray['transcriber_overtime'];


echo('<tr>

    <td align="center" style="border:1px solid #000000; padding:3px">$'.$staffrate.'</td>
    <td align="center" style="border:1px solid #000000; padding:3px">');

$invoicequery = mysqli_query($con, "SELECT * FROM kmis_invoices WHERE invoice_staffid='$staffid'");
  while ($invoicearray = mysqli_fetch_array($invoicequery)) {
           echo(''.$invoicearray['invoice_hours'].'');
}
   echo('</td>
         <td align="center" style="border:1px solid #000000; padding:3px">');

$invoicequery = mysqli_query($con, "SELECT * FROM kmis_invoices WHERE invoice_staffid='$staffid'");
  while ($invoicearray = mysqli_fetch_array($invoicequery)) {
           echo(''.$invoicearray['invoice_overtime'].'');
}
      echo('</td> 
        <td align="center" style="border:1px solid #000000; padding:3px">');

$invoicequery = mysqli_query($con, "SELECT * FROM kmis_invoices WHERE invoice_staffid='$staffid'");
  while ($invoicearray = mysqli_fetch_array($invoicequery)) {
           $invoicehours= $invoicearray['invoice_hours'];
           $invoiceovertime= $invoicearray['invoice_overtime'];

    $subtotal = ($invoicehours*$staffrate)+($invoiceovertime*$staffrate*1.5);
    $subtotalformat = number_format((float)$subtotal, 2, '.', '');

      echo('$'.$subtotalformat.'');
} 
   echo('</td>
   </tr>');
}
    echo('<tr>
        <td align="center" style="border:1px solid #000000; padding:3px"></td>
        <td align="right" style="border:1px solid #000000; padding:3px" colspan="4"><b>TOTAL DUE:</b></td>
        <td align="center" style="border:1px solid #000000; padding:3px"><b>TOTAL</b></td>
        </tr>

You can see that the formula I use to create the subtotal column uses four different queries -- from invoices and from staff. Some staff have different rates, so the subtotal is based on data that could be entirely different row to row.

I think the answer lies in array_sum and using an array to hold all of the $subtotal when they're populated but I'm completely stumped.

Thank you sincerely. Please let me know if you need me to clarify or help make this more precise.

Solution

I have read through your problem.The problem seems quite simple to me but your code is all messed up.What I understood was your total will be the sum of all the subtotal values,So

Total= 40$ + 108$ = 148$ //from your shown data in fig

Initialize a variable $total outside your for loop

  $total=0;
  for($i=0; $i<$staffcount; $i++) {

After you calculate your subtotal value, add this to your total as given below

$subtotal = ($invoicehours*$staffrate)+($invoiceovertime*$staffrate*1.5);
$total=$total + $subtotal; 
$subtotalformat = number_format((float)$subtotal, 2, '.', '');

Finally put the value of calculated $total in the result as

 echo('<tr>
        <td align="center" style="border:1px solid #000000; padding:3px"></td>
        <td align="right" style="border:1px solid #000000; padding:3px" colspan="4"><b>TOTAL DUE:</b></td>
        <td align="center" style="border:1px solid #000000; padding:3px"><b>TOTAL : '.$total.'</b></td>
        </tr>

Your php code appears very lengthy and messed up,so I suggest you to try this query to make it short.

SELECT 
    t1.transcriber_id staff,
    t1.staff_position position,
    t1.staff_rate Rate,
    t2.invoice_hours hours,
    t2.invoice_overtime 'OT x 1.5',
    (t2.invoice_hours * t1.staff_rate) + (t2.invoice_overtime * t1.staff_rate * 1.5) subtotal
FROM
    kmis_transcribers t1
        JOIN
    kmis_invoices t2 ON (t1.transcriber_id = t2.invoice_staffid)
WHERE
    t1.transcriber_projectid=1 //$projectinvoiceid in your case

The above query will return the view that you want and your php code will be much simple now as now instead of four queries you need only one query.