CODEWITHSUNDEEP
phpmysqlsql-updatepercentage
Pepe
Pepe

Reputation: 1002

Update percentage column in database table based on count column of all qualifying rows

I want to update the percentage column based on the count data for all is_enabled = 1 rows in a table.

enter image description here

My coding attempt looks like this:

<?php 
    $total = '';
    $result= mysqli_query($conn, "SELECT SUM(count) FROM My_Databse WHERE is_enabled ='1'");
    while($row = mysqli_fetch_array($result)){
        $total = $row['SUM(count)'];
    }
    
    $percentage = '';
    $result= mysqli_query($conn, "SELECT * FROM My_Database WHERE is_enabled ='1' ORDER BY count DESC");
    while($row = mysqli_fetch_array($result)){
        $percentage = ($row[2] / $total) * 100;
        echo '<div class="progress">';
        echo '<div class="progress-bar" role="progressbar" aria-valuenow="'.$percentage.'" aria-valuemin="0" aria-valuemax="100" style="width:'.$percentage.'%">';
        echo $row[1].'('.round($percentage).')';
        echo '</div>';
        echo '</div>';
        $i++;
    }
    $result = mysqli_query($conn, "UPDATE My_Database SET percentage = ".$percentage." WHERE id = 1");
?>

I have now the problem, that I always get the last percentage. How can I update the percentage for every row?

Upvotes: 1

Views: 483

Answers (2)

mickmackusa
mickmackusa

Reputation: 47992

I do not recommend making multiple trips to your database, nor using php for a task that can be simply, efficiently, directly, and completely done with a single query.

Join a derived table containing the count total for all qualifying rows, then build the arithmetic to calculate the percentage and update the rows accordingly.

It is more efficient to join the derived table versus calling the subquery for each qualifying row.

Code: (DB-Fiddle)

UPDATE my_table
JOIN (SELECT SUM(`count`) total FROM my_table WHERE is_enabled = 1) all_enabled
SET percentage = ROUND(`count` / total * 100)
WHERE is_enabled = 1;

New table data:

id tutorial count is_enabled percentage
1 House 3 1 6
2 Car 34 1 68
3 Tuna Fish 22 0 0
4 Bike 13 1 26

Depending on your circumstances (how often this table is read and written to), you might rather declare a TRIGGER to auto calculate&update the percentage column whenever count or is_enabled values are changed or a new row with is_enabled is INSERTed.

Upvotes: 1

Isha
Isha

Reputation: 161

Update query must be within while loop, after calculating percentage -

mysqli_query($conn, "UPDATE My_Database SET percentage = ".$percentage." WHERE id = ". $row['id'] );

Upvotes: 1

Related Questions