How to create transaction like values in MySQL or php using last row value to calculate

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.

score 1 · Accepted Answer · answered Mar 24 '13 at 01:09

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.

score 1 · Answer 2 · answered Mar 24 '13 at 01:14

1

I would calculate the balance using a SELECT, something like

select (currBalance + debit + gain) total_balance from chktrans;

If you want to fetch the last row (the last inserted), you could do something like:

select * from chktrans where rowid in (select max(rowid) from chktrans);

Hope it helps.

answered Mar 24 '13 at 01:14

ophintor

181
1
10

helps greatly. i would mark both as answers if i could. appreciated – Justin Mar 24 '13 at 02:06

How to create transaction like values in MySQL or php using last row value to calculate

2 Answers2