Calculate cash balance

[rhandyx]

Hi I have one mysql table like this

|id| - |description| - |operator(+/-)| - |amount| - | balance |
|2| - | sale # 2 | | + | |100.00| | 150.00 |
|1| - | sale # 1 | | + | | 50.00| | 50.00 |

my “problem is” if need to modify one amount in middle of table it needs to recalculate and update all balance column

I make one

$query=“select * from .”.$tablename. "order by id ASC ";

if ($result = $conn->query($query)){;

			 while ($row = $result->fetchassoc() ){
					 
					 if($row['operator']=="+"){
				 		$balance=$balance+$row['ammount'];
						 $balance=round($balance, 2);
					 }else{
						 $balance=$balance-$row['ammount'];
						 $balance=round($balance, 2);
					 }
					 
			
					 $queryupdate_balance="UPDATE `$tablename` SET `balance`=" .$balance. " where id=".$row['id'] ; 
					 echo  $queryupdate_balance. "<br>";
					query->($conn,$queryupdatecx);
			 }
			}

The problem is this could be too slow if I have thousand records

[phdr]

Don’t store ‘derived’ values. The balance is a derived value. Just calculate it in a query whenever you need it, using sql query functions, such as SUM(). You should just store the amount as a positive or negative number and forget about the operator column. You should also have an item/account/category id column (something tells me you have created a separate table for each different item/account/category.)

[rhandyx]

is one idea. not need to store it.

I´m asking because the db I Import have that configuration, and i´m try to understand how the old program do it before.