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

Question

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

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?

Hint: `$percentage` only exists _inside_ your `while` loop... — ADyson, Mar 04 '22 at 09:12
Hint 2: To get a total you can use [SUM](https://www.mysqltutorial.org/mysql-sum/) in the SQL, rather than laboriously looping through every row — ADyson, Mar 04 '22 at 09:15
Thx for your help. Can you show me your SUM example? Ah, thx for the Link .. — Pepe, Mar 04 '22 at 09:16
I can't imagine using PHP for this task. This should all be done in a single sql query. Maybe derive some insights from: https://stackoverflow.com/q/15716115/2943403, https://stackoverflow.com/q/20259848/2943403, https://stackoverflow.com/q/27217264/2943403, https://stackoverflow.com/q/24267955/2943403, https://stackoverflow.com/q/12998146/2943403 — mickmackusa, Mar 04 '22 at 09:24
I have updated my sql with SUM, but not it doesnt work any longer. Whats my fail? — Pepe, Mar 04 '22 at 09:39
That's because you still need two queries - one to get the SUM of everything and one to then loop through the individual records and calculate the percentage for each row. But yeah you can probably do it a nicer way just in pure SQL as mickmackusa points out. — ADyson, Mar 04 '22 at 10:21
I start to understand now, I have updated it again. Now it works fine (maybe not the best way, but it works). My only problem is now to get the right percentage for every row. — Pepe, Mar 04 '22 at 10:28

score 1 · Accepted Answer · edited Mar 04 '22 at 10:45

1

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

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

edited Mar 04 '22 at 10:45

ADyson

57,178
14
51
63

answered Mar 04 '22 at 10:41

Isha

99
1
9

Thx for your help. Why downvote? It makes sense what he is saying and showing (and it works). Is it just not a good practice way, because of the loop? – Pepe Mar 04 '22 at 10:45
1

I accept this one - thx for your explanation AND showing, because sometimes its also important to see the structure. – Pepe Mar 04 '22 at 10:51
1

I did not dv, but this is not best practice for a few reasons. There is no prepared statement and as I said under the question, making iterated queries is not good for performance/resources, and this can and should be done entirely in sql. – mickmackusa Mar 04 '22 at 13:52

mickmackusa · Answer 2 · 2022-03-06T11:29:32.510

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.

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

2 Answers2

Linked