line calculations

[DrTrans]

The following code works, it provides me the SUM Amount of all the records in the database.  What i need to know is how to show a Line Balance.  for example

 

id message pay_amount  pay_balance

5  Paid        $500              $300              < - i can get this to show for all ID's as the balance ,

4  Debt      -$100            $-200            <- I need this line balance

3  Debt      -$100            $-100

2  Balance  0                      0   

 

 

Any Help is greatly appreciated.

 

Heres my code:

 

   $query = "SELECT * FROM payments WHERE propid = '$propid' ORDER BY id DESC ";
        $result = mysql_query($query);
        while($row = mysql_fetch_assoc($result))
	{
	    $pay_id		 	= $row['id'];
		$propertyid     = $row['propid'];
		$pay_message   		= $row['pay_message'];
		$pay_smessage 	    = substr($pay_message,0, 15);
		$pay_who 		= $row['pay_who'];
		$pay_date 		= $row['pay_date'];	
		$pay_amount		= $row['pay_amount'];	
		$pay_controller = $row['pay_controller'];						

		if($pay_controller == "-") {

			$fontcolor = "red";
		} 
		if($pay_controller == "+") {

		   $fontcolor = "black";
		}

        $query9 = "SELECT SUM(pay_amount) FROM `payments` WHERE propid = '$propid'"; 
		$result9 = mysql_query($query9);
        $sum = mysql_result($result9,0);
            $sum2 =  number_format($sum,2);




print"<tr  onMouseOver=\"this.bgColor = '#CEE3F6'\" onMouseOut =\"this.bgColor = '#FFFFFF'\">
<td onclick=\"window.alert('Amount: $pay_amount Message: $pay_message')\">$pay_id</td><td>$pay_smessage</td><td>$pay_date</td><td><font color=\"$fontcolor\">$$pay_amount</font></td><td>$sum2</td>

";
  }

 

 

[requinix]

Side comment: your table is a bit off and your signs are backwards (from what I can tell). Such a table normally looks more like

Balance: $300

ID | Message | Amount | New Balance
---+---------+--------+------------
5 | Paid    | -$500  | -$200
4 | Debt    |  $100  | -$100
3 | Debt    |  $100  |  $0

It starts off with an initial balance and changes for each transaction.

 

 

But that's irrelevant. For the initial problem, just do the math as you run through the results.

$pay_balance -= $pay_amount;

[DrTrans]

Got it working..

 

however, it does not carry over if its a negative or positive balance.

 

 

[requinix]

Then you should probably fix that. What's your code now?