如何使用最后一行值来计算在MySQL或php中创建类似值的事务

Question

New to MySQL/php but I am using xampp and trying to create a simple webpage to act like ledger (like checking account kind of deal). So I have debit, gain and balance columns (and some others) in a table which I am inserting a new record:

html

<form id='chk-trans-form' action="example-api.php" method="post">
Detail: <input type='text' name='trans-detail'/><br/>
Date: <input type='text' id='trans-dts'/><br/>
Amount: <input type='text' id='trans-amount'/>
Debit <input type='radio' name='trans-type-group' value='debit' checked/> Gain <input type='radio' name='trans-type-group' value='gain'/>
<button id='submit-trans'>submit</button>
</form>

database.php

<?php
   $conn = mysql_connect('localhost', 'root', '');
   $db   = mysql_select_db('financial');
?>

example-api.php

<?php
    include_once('database.php');

    $transDate = $_POST['trans-dts'];
    $transDetail = $_POST['trans-detail'];
  $transAmount = $_POST['trans-amount'];
  $transType = $_POST['trans-type-group'];

  if ($transType == 'debit')
  {
      if(mysql_query("INSERT INTO chktrans (chkDTS, chkDetail, chkDebit) VALUES ('$transDate','$transDetail', '$transAmount')"))
          echo "Successfully Added";
      else
          echo "Insertion Failed"; 
   } else {
    if(mysql_query("INSERT INTO chktrans (chkDTS, chkDetail, chkGain) VALUES ('$transDate','$transDetail', '$transAmount')"))
        echo $transType;
        else
            echo "Insertion Failed";    
   }
?>

so this was the approach i learned and it works well for getting the data to the table, though I am not sure if its the best approach, just what I found.

So the next part I am a little unsure about....since this is a transaction like table, I need something like Last Balance + (Debit + Gain) = new balance. So I am not sure if this is something that I should be trying to get in php, or do in mysql....I have found triggers for a calculated-like field approach (here), but I am not sure if that will work because I don't know how to get the last value, or if the best approach is creating triggers for this, or trying to pull the value and use php to get the value, calculate and send everything back??

any help or direction is appreciated as always.

Answer 1

Just SELECT the column that you want, store that in a variable, get the other. In this case you'll be getting the 'chkGain' and the necessities for the debit. The last balance will remain the same until the statement is finished invocation.

$currBalance = $currBalance + $debit + $gain;

Note that in your equation, 'Last Balance + (Debit + Gain),' parentheses aren't needed. Order of operations, my friend.