Php mysql add /subustract error

dmanghani · September 23, 2020, 8:52am

Hi,
I am trying to create a passbook type display. which has input for data and amount, along with Deposit , Withdraw button.

After user presses the Deposit/ Withdraw button,
the code adds or subtracts mysql record two times.
If I press refresh button data is added again, how do i prevent this?
Any help would be appreciated.!
help please

<?php 

setlocale(LC_MONETARY, 'en_IN');
date_default_timezone_set('Asia/Kolkata');
include "config.php"
?>
<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <link rel="stylesheet" href="style.css" type="text/css" />
</head>
<main class="section main">
                <table class="tg">
                    <colgroup>
                        <col style="width: 90px"  />
                        <col style="width: 200px" />
                        <col style="width: 100px" />
                        <col style="width: 100px" />
                        <col style="width: 120px" />
                        <col style="width: 90px" />
                    </colgroup>
                    <thead>
                       
                    </thead>
                    <tbody>
                        <tr>
                            <td class="tg-h2xt">Date</td>
                            <td class="tg-h2xt">Particulars</td>
                            <td class="tg-h2xt">Deposit</td>
                            <td class="tg-h2xt">Withdraw</td>
                            <td class="tg-h2xt">Balance</td>
                            <td class="tg-h2xt">Action</td>
                        </tr>
                        <tr>
                        <?php 

                $sql = "SELECT * FROM payments";
                $result_data=mysqli_query($conn, $sql);
                
                while ($row_data = mysqli_fetch_assoc($result_data)) {
                    $trans_date=$row_data['trans_date'];
                    $deposit=$row_data["Deposit"];
                    $withdraw=$row_data["Withdraw"];
                    $balance=$row_data["Balance"];
                    echo "<tr>";
                    echo "<td class='tg-0pky'>" .$trans_date. "</td>";
                    echo "<td class='tg-0pky'>" .$row_data["Particulars"]. "</td>";
                    echo "<td class='tg-dvpl'>" .number_format($deposit,2). "</td>";
                    echo "<td class='tg-dvpl'>" .number_format($withdraw,2). "</td>";
                    echo "<td class='tg-dvpl'>" .money_format('%!i',$balance). "</td>";
                    echo "</tr>";
                    }
                    echo "</table>";

                    echo "<footer class='section footer'>";

        $sql = "SELECT Balance FROM payments WHERE id=(SELECT MAX(id) from payments)";
        $result_data=mysqli_query($conn, $sql);
       
          while($row_data = mysqli_fetch_assoc($result_data)) {
              $balance = $row_data["Balance"];

              if (isset($_POST['deposit'])) {
                $date=$_POST['trans_date'];
                $amount=$_POST['amount'];
                $particular=$_POST['particular'];
                $deposit=$amount;
                $total=$balance+$amount;
                
                $query = "INSERT INTO payments (trans_date,Particulars,Deposit,Balance) VALUES ('$date','$particular','$amount','$total')";
                mysqli_query($conn,$query);
                if($sql_add) {
                                echo 'data added!';}
              if (isset($_POST['withdraw'])) {
                $date=$_POST['trans_date'];
                $amount=$_POST['amount'];
                $particular=$_POST['particular'];
                $deposit=$amount;
                $total=$balance-$amount;
                $query = "INSERT INTO payments (trans_date,Particulars,Withdraw,Balance) VALUES ('$date','$particular', '$amount', '$total')";
                mysqli_query($conn,$query);
                if($sql_sub) {
                    echo 'data subsctracted';
                }
              }
            }
          }
            ?>   
       
            <form name "payment" action "" method="POST"> 
                Transaction Date:
            <input type="date" name="trans_date"  required=" " />  
                Transaction Particulars:
                <input type="text"  name="particular"  required=" " /> 
                 Transaction Amount:
                                <input type="number" placeholder="000.00" name="amount" required=" " />
                <input type=submit name="deposit" value="Deposit" />
                <input type=submit name="withdraw" value="Withdraw" />
                <input type=reset  name="Reset" value="Reset" />
            </form> 
           
            </footer>

phdr · September 23, 2020, 9:22pm

After you submit the form, how are you viewing the results, since the query that displays all the data is above the form processing code? Your code should be arranged with the post method form processing code above the start of the html document.

Does that mean that there are two inserted rows or just that the balance calculation has had the amount added/subtracted twice?

Is there more code on the page, such as for an UPDATE or DELETE query that you didn’t post, that could be affecting the data?

By doing a header() redirect to the exact same url of this page after successfully processing the form data, to cause a get request for the page.

Lastly, the running balance is a ‘derived’ value. You should not store it in the database table, as any update/delete of any of the existing data will make the stored values incorrect. You should calculate it when needed. You can do this by using an sql user variable in the query, and add/subtract each successive amount to it to get the current balance for each row.