Increment in fetch array while loop

Question

I have trouble to increment correctly a variable in a while loop where I fetch data from my database.

This is the code:

while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    //January
    if ($row['month'] ==1){
        if ($row['day_range'] == '01-07') {
            $val1_1 = $row['duration'];
            $int1_1 = $row['intensity'];
        }
        elseif ($row['day_range'] == '08-14') {
            $val1_2 = $row['duration'];
            $int1_2 = $row['intensity'];
        }
        elseif ($row['day_range'] == '15-21') {
            $val1_3 = $row['duration'];
            $int1_3 = $row['intensity'];
        }
        elseif ($row['day_range'] == '22-end') {
            $val1_4 = $row['duration'];
            $int1_4 = $row['intensity'];
        }
        //Avg intensity
        $int1 = ($int1_1 + $int1_2 + $int1_3 + $int1_4)/4;
    }
}

So I have this code for every month.
The problem here is that sometimes I don't have 4 values, so at the end when I calculate the AVG it is sometimes wrong because it always divides it by 4

What I've done :

I had the idea of incrementing a variable $i each time I have a value, like this

$i = 0;
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    //January
    if ($row['month'] ==1) {
        if ($row['day_range'] == '01-07') {
            $val1_1 = $row['duration'];
            $int1_1 = $row['intensity'];
            $i++;
        }
        elseif ($row['day_range'] == '08-14') {
            $val1_2 = $row['duration'];
            $int1_2 = $row['intensity'];
            $i++;
        }
        elseif ($row['day_range'] == '15-21') {
            $val1_3 = $row['duration'];
            $int1_3 = $row['intensity'];
            $i++;
        }
        elseif ($row['day_range'] == '22-end') {
            $val1_4 = $row['duration'];
            $int1_4 = $row['intensity'];
            $i++;
        }
        //Avg intensity
        $int1 = ($int1_1 + $int1_2 + $int1_3 + $int1_4)/$i;
    }
    $i = 0;
    //code for next month
}

But it doesn't work, I've echoed it and $i stays at 1.
I think it is because it fetch row by row so it never goes through all incrementations to reach the value desired.

How can I do that please?

Answer 1

// Here is an array to get the data of EACH month
$month = array();
$i = 0;
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    // This way, you will have $month[1] for January, $month[2] for February, etc.
    $month[$row['month']][] = array(
        "duration" => $row['duration'],
        "intensity" => $row['intensity'];
    );
}

// Now you get all the data, you can calculate the Avg intensity for each month
foreach ($month as $month_number => $data) {
    $moy = count($month[$month_number]); // Will be 4 if you have 4 period, 3 if only 3, etc.
    $sum = 0;

    foreach ($data as $value) {
        $sum += $value['intensity'];
    }

    $month[$month_number]['avg_intensity'] = $sum / $moy;
}

With this method you should get an array with all the data you want that look like this :

$month = array(
    // January
    1 => array(
        0 => array(
            'duration' => ...,
            'intensity' => ...
        ),
        1 => array(
            'duration' => ...,
            'intensity' => ...
        ),
        ...
        'avg_intensity' => /* moy of all intensity of the month */
    ),
    // February
    2 => array(
        ...
    ),
    ...
);

Hope it helps you !

EDIT :

As suggested if the comment by Nigel Ren, you can replace

$sum = 0;
foreach ($data as $value) {
    $sum += $value['intensity'];
}

By

$sum = array_sum(array_column($data, "intensity"));