PHP loop for calculating averages of values in html table

Question

I have a page on my site that displays multiple html tables. One of these tables houses test values pulled from the database. The last row of this table is calculated by php, which I've been using based on the 8 tests. However, now the tests are not set at 8 so they may sometimes be 4,5 or up to 8 but there's no way to know. I have my PHP loop calculating the average by 8 but I need it to probably base the formula off of an actual count.

The PHp loop in the code below works great if there are values for all 8 tests. As you can see, it divides two row elements, stores that answer and then multiplies it by another. The math is working, I just need help getting it to count rather than using a static number of 8.

Here is the code:

//Table

$query1 = "SELECT * FROM staging";
$result1 = mysqli_query($connect,$query1);

while($row = mysqli_fetch_array($result1)){
?>//PHP loops around entire table

<table style="width:100%; border:none;
border-collapse:collapse;">

    <tr style="border:none;">
        <th style="border:none; text-align: left;" >Meter Test</th>
        <th style="border:none;">Test 1</th>
        <th style="border:none;">Test 2</th>
        <th style="border:none;">Test 3</th>
        <th style="border:none;">Test 4</th>
        <th style="border:none;">Test 5</th>
        <th style="border:none;">Test 6</th>
        <th style="border:none;">Test 7</th>
        <th style="border:none;">Test 8</th>
    </tr>
    <tr style="border: none;" >
        <td style="border:none; text-align: left;">Test Rate GPM: </td>
        <td><? echo $row['test1TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test2TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test3TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test4TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test5TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test6TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test7TestRateGPM'];?>&nbsp;</td>
        <td><? echo $row['test8TestRateGPM'];?>&nbsp;</td>

    </tr>
    <tr>
        <td style="border:none; text-align: left;">Meter Volume: </td>
        <td><? echo $row['test1MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test2MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test3MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test4MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test5MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test6MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test7MeterVol'];?>&nbsp;</td>
        <td><? echo $row['test8MeterVol'];?>&nbsp;</td>
    </tr>
    <tr>
        <td style="border:none; text-align: left;">Tester Volume: </td>
        <td><? echo $row['test1TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test2TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test3TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test4TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test5TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test6TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test7TesterVol'];?>&nbsp;</td>
        <td><? echo $row['test8TesterVol'];?>&nbsp;</td>
    </tr>
    <tr>
        <td style="border:none; text-align: left;">Tester Accuracy: </td>
        <td><? echo $row['test1Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test2Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test3Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test4Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test5Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test6Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test7Accuracy'];?>%&nbsp;</td>
        <td><? echo $row['test8Accuracy'];?>%&nbsp;</td>
        <td style="border:none;">Overall</td>
    </tr>
    <tr>
        <td style="border:none; text-align: left;">Corrected Accuracy: </td>

//Previous code only for html tables and the database values being pulled

/////////

//Following code is the loop in question

<?php 
        $sum=0;
        for($i=1; $i<=8; $i++){ 
        if($row["test".$i."TesterVol"] == 0){
  $row["test".$i."TesterVol"] = 0.001;

 }
            $testFormA = $row["test".$i."MeterVol"] /       $row["test".$i."TesterVol"]; 
                $testFormB = $testFormA * $row["test".$i."Accuracy"]; 
                $testFinalForm = $testFormB;
                $sum += $testFinalForm; 

        ?>
        <td><?php echo round($testFinalForm,3) ?>%</td>


        <?php }
        $average = $sum / 7;

        ?>

        <td><?php echo round($average,5)?>%&nbsp;</td>

    </tr>
</table>

UPDATE

Screenshot of the table:

FYI, your code is wide open to SQL injection attacks. Your database has likely already been hijacked. You must escape arbitrary data used in the context of a query. Using parameterized queries, you don't have to worry about this. — Brad
– Brad, Commented May 23, 2017 at 3:30
I slightly updated the code at the beginning to show the query, connection and result set and also the while loop that covers all the tables. That's all the code there is to post for this, so I'm not sure what else I can post. — Geoff_S
– Geoff_S, Commented May 23, 2017 at 3:32
Thanks for the update. Now I'm really confused... what's in these rows? Sounds like you've jammed an entire table of data into every row? You have both dimensions (multiple tests and the test type) in each row? — Brad
– Brad, Commented May 23, 2017 at 3:36
you can use count for($i=1, $n=count($row); $i<=$n; $i++){ — bansi
– bansi, Commented May 23, 2017 at 3:40
@TomN. Alright, so this data structure is all crazy complicated. :-) I'd strongly recommend refactoring your database structure. In the mean time, let me see if I can whip up some reasonable code to fix it and get you on the right track... give me a few minutes. — Brad
– Brad, Commented May 23, 2017 at 3:42

Brad · Accepted Answer · 2017-05-23 04:40:32Z

Alright, I have a solution for you.

<?php

// Import existing data structure
$data = [
  // Test Rate GPM
  test1TestRateGPM => 0.5,
  test2TestRateGPM => 5,
  test3TestRateGPM => 10,
  test4TestRateGPM => 15,
  test5TestRateGPM => 25,
  test6TestRateGPM => 100,
  test7TestRateGPM => 150,
  test8TestRateGPM => 0,

  // Meter Volume
  test1MeterVol => 0,
  test2MeterVol => 0,
  test3MeterVol => 0,
  test4MeterVol => 2.74,
  test5MeterVol => 4.95,
  test6MeterVol => 15.7,
  test7MeterVol => 33.5,
  test8MeterVol => 0,

  // Tester Volume
  test1TesterVol => 0.5,
  test2TesterVol => 1,
  test3TesterVol => 2,
  test4TesterVol => 4.04,
  test5TesterVol => 6.7,
  test6TesterVol => 20,
  test7TesterVol => 40.1,
  test8TesterVol => 0,

  // Tester Accuracy
  test1Accuracy => 1,
  test2Accuracy => 1,
  test3Accuracy => 1,
  test4Accuracy => 1,
  test5Accuracy => 1,
  test6Accuracy => 1,
  test7Accuracy => 1,
  test8Accuracy => 0
];

// Make data structure sane...
// One row per test.  Each row to contain the relevant fields

// Figure out the lowest and highest indices based on the key names, and the keys we want
$lowIndex = INF;
$highIndex = -INF;
$keys = [];
foreach ($data as $key => $value) {
  $matches = [];
  if (preg_match('/^test([0-9]+)(.*)$/', $key, $matches)) {
    $index = (int) $matches[1];
    $lowIndex = min($lowIndex, $index);
    $highIndex = max($highIndex, $index);
    $keys[] = $matches[2];
  }
}
$keys = array_unique($keys);

// Loop through to fix the data
$tests = [];
for ($i = $lowIndex; $i <= $highIndex; $i++) { // Assumes contigous indices!
  $test = (object)[];
  foreach ($keys as $key) {
    $test->{$key} = $data['test' . $i . $key];
  }
  if ($test->TesterVol) {
    $test->CorrectedAccuracy = $test->MeterVol / $test->TesterVol;
  }
  $tests[] = $test;
}

// Yeah!  Now we have some sane data.  For example, if you use print_r($tests)...
// Array
// (
//     [0] => stdClass Object
//         (
//             [TestRateGPM] => 0.5
//             [MeterVol] => 0
//             [TesterVol] => 0.5
//             [Accuracy] => 0.5
//             [CorrectedAccuracy] => 0
//         )
//
//     [1] => stdClass Object
//         (
//             [TestRateGPM] => 5
//             [MeterVol] => 0
//             [TesterVol] => 1
//             [Accuracy] => 5
//             [CorrectedAccuracy] => 0
//         )
// ...


// Output the table!
echo '<table>';


// Header Row
echo '<thead><tr>';
echo '<th>Test #</th>';
foreach ($keys as $key) {
  echo '<th>', htmlspecialchars($key), '</th>';
}
echo '</tr></thead>';

// Main data
echo '<tbody>';
foreach ($tests as $testIndex => $test) {
  echo '<tr>';
  echo '<td>', htmlspecialchars($testIndex), '</td>';
  foreach ($keys as $key) { // Using this original array of keys ensures we stay in-order
    echo '<td>', htmlspecialchars($test->{$key}), '</td>';
  }
  echo '</tr>';
}
echo '<tr><td>Average</td>', str_repeat('<td>&nbsp;</td>', count($keys) - 1), '<td>';
echo array_sum(array_map(function ($test) {
  return $test->CorrectedAccuracy;
}, $tests)) / count($tests);
echo '</td></tr>';
echo '</tbody>';
echo '</table>';

Correct your database schema. Once you've done that, this is much simpler.

This code re-works your existing schema so that each test is its own record. Once it's in that format, you can use all the standard mechanisms such as map/reduce to work with the data. You could even do all of that within MySQL.

Other things to note... always use htmlspecialchars() around arbitrary data you output in the context of HTML. You're dealing with numbers today, sure, but some day you might not be and the code doing the outputting should handle this correctly.

Also in one of your edits, you were using $_REQUEST data directly in your query, unsanitized or escaped or anything. Switch to parameterized queries to avoid serious security issues. It's very likely your database has already been hijacked, as bots are always on the internet looking for sites like yours to exploit.

If you really want to have the data going the wrong direction, I'll leave flipping that around as an exercise to the reader. :-) But, post a comment if you have a question.

Perfect, thank you! I will put this into practice and this will definitely help me in the future as well

Collectives™ on Stack Overflow

PHP loop for calculating averages of values in html table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related