laravel average with null value

user_9550226 0
Viewed 182

i'm trying to do average in each row from my database table. but it is not working properly i wanted to ignore the value if null it will not calculate as zero. with my code it is calculating the null value as zero i want to do it like MS Excel where if row/cell is empty it will ignore.

Controller code:

public function update_average(Request $request) 
        { 
          $scores = $request->input('scores'); //here scores is the input array param 
          foreach($scores as $row){ 
          $score = cie_score::find($row['id']); 
         
          $score->term2_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null]) /2);
          $score->term3_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null] + $row['term3_result'?: null])/3);
          $score->term4_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null] + $row['term3_result'?: null] + $row['term4_result'?: null])/4);
       
          $score->save(); 
          }
          
        

          return redirect()->back();
        }

Image result enter image description here

laravel average
1 Answers

You can calculate what you are looking for like follows:

    $currentTerm = 4;
    $scores = $request->input('scores');

    for ($term = 1; $term <= $currentTerm; $term++) {
        foreach ($scores as $row) {
            $score = cie_score::find($row['id']);

            $total = 0;
            $terms = 0;

            for ($i = 1; $i <= $term; $i++) {
                $total += $row['term'.$i.'_result'] ?? 0;
                $terms += empty($row['term'.$i.'_result']) ? 0 : 1;
            }

            $key = 'term'.$term.'_average';
            
            if ($terms > 0) {
                $score->$key = round($total/$terms);
            } else {
               $score->$key = 0;
            }
           
            $score->save();
        }
    }

This will calculate averages for all terms from 1 to the current term. If a 'term{$i}_result' is null, then it is ignored in the total, and wont count towards the divisor.

It is more efficient for your db to do the following:

    $currentTerm = 4;
    $averages = [];
    $scores = $request->input('scores');

    for ($term = 1; $term <= $currentTerm; $term++) {
        foreach ($scores as $row) {
            $id = $row['id'];
            if (!isset($averages[$id])) {
                $averages[$id] = [];
            }

            $total = 0;
            $terms = 0;

            for ($i = 1; $i <= $term; $i++) {
                $total += $row['term'.$i.'_result'] ?? 0;
                $terms += empty($row['term'.$i.'_result']) ? 0 : 1;
            }

            $key = 'term'.$term.'_average';
            
            if ($terms > 0) {
                $averages[$id][$key] = round($total/$terms);
            } else {
                $averages[$id][$key];
            }
        }

        foreach($averages as $id => $data) {
            cie_score::where('id', $id)
                     ->update($data);
        }
    }
user_19783110
Related