I am trying make code in php with mysql database, and i want to calculate balance with stored value and new inputed value. for example i have database like this:
id | debit | credit | balance
----+---------+--------+--------
1 | 70000 | 0 | 70000
2 | 0 | 44000 | 26000
3 | 45000 | 15000 | 56000
4 | 0 | 32000 | 24000
5 | 0 | 10000 | 14000
6 | 28000 | 0 | 42000
the formula is balance = old_balance + new_inputed_debit - new_inputed_credit
so if i inputed new value like this in html form:
debit = 30000
credit = 5000
so the script will sum this when i click input button balance = 42000 + 30000 - 5000
And the database will become like this:
id | debit | credit | balance
----+---------+--------+--------
1 | 70000 | 0 | 70000
2 | 0 | 44000 | 26000
3 | 45000 | 15000 | 56000
4 | 0 | 32000 | 24000
5 | 0 | 10000 | 14000
6 | 28000 | 0 | 42000
7 | 30000 | 5000 | 67000 <-- New Submited Value
this is my php script:
<?php
require('db2.php');
// If form submitted, insert values into the database.
if(isset($_POST['submit'])) {
$debit = mysqli_real_escape_string($con,$_POST['debit']);
$credit = mysqli_real_escape_string($con,$_POST['credit']);
$balance = //old_balance + new_inputed_debit - new_inputed_credit
$query = "INSERT into `balance` (debit, credit, balance,) VALUES ('$debit','$credit', '$balance')";
if(mysqli_query($con,$query)){
echo "<div class='form'><h3>Inputed Success</h3><br/><a href='Input.php'>Input again</a></div> or <a href='index.php'>back</a>";
}else{
}
}
?>
And this is my html code:
<form name="input_data" action="" method="post"><br />
<label style="font-size:16px;">Debit :</label>
<input type="text" name="debit" placeholder="Ex: 90000" style="margin-left:3px; width:80% !important;" required /><br />
<label style="font-size:16px;">Credit :</label>
<input type="text" name="credit" placeholder="Ex: 90000" style="margin-left:3px; width:80% !important;" required /><br />
<span style="float:right;"><input type="reset" name="reset" value="RESET" style="margin-right:8px;" /> <input type="submit" name="submit" value="INPUT" style="margin-right:8px;" /></span>
</form>
Is anyone can help me to create the php script? thanks.
initialize balance with 0 in case you have no rows in table. after that get last row from table and get old_balance from that row. just place this script after $credit = .... line. you will get old_balance from last row or 0 if there will be no rows.
$old_balance = 0;
$sql = "select max(id), balance from balance_table";
$result = mysqli_query($con,$sql);
if(mysqli_num_rows($result) == 1){
$row = mysqli_fetch_assoc($result);
$old_balance = $row["balance"];
}