1

I am having a hard time figuring this one out. I am trying to make a financial tracking system (for a small business) and I am having an issue. Basically the way that I have it set up is that the user will input a payout, or the money they have received for the week, and put it into the database. Then the user will withdraw money and I have an update query for that. The issue is that when the update query is ran, and the user refreshes the page in order to see the updated amount still available to withdraw, the update query is ran again and again and again and it is not stopped.

What I am trying to have is that:

  • The user will insert the payout for the week, and that will display in the "Total Amount of Payouts" element.
  • Then they will enter the amount that they had withdrawn from the amount in the bank using the "Withdrawal From Bank" form. Once that update query is released, I would like the new amount of the "Total Amount of Payouts" to be updated in order to show the user the amount remaining that is available for withdrawal. The issue I am running into is here as when the user hits the submit button, and refreshes the page (in order to see the updated amount available to be withdrawn from "Total Amount of Payouts", the update query is ran every time that the page is refreshed, causing the database and page to show negative values.
  • The user will then enter the amount of cash spent using the "Cash Spent" form to show the amount of cash remaining that the user has available.

I have tried ending the session once the submit button is pressed and using a header redirect. But that does not stop the refreshing of the page from updating the database. I want to only update the database when the button is pressed on and the page not refreshed. I only want the updated database values to be shown when the page is refreshed, not update the database. How would I achieve this?

Another issue that I am having is that when the database is updated, it takes the total amount of every int in the bank_withdraw row within the database. I know this is due to the SUM that I have set up, how would I get it to only show the latest withdraw value?

Please excuse the mess. I am fairly new to PHP and SQL and I am learning. Thank you for your time and any help is greatly appreciated!

The PHP code I have currently:

    $conn = mysqli_connect($host, $username, $password, $dbname);
    
    if (mysqli_connect_errno()) {
        die("Connection error: " . mysqli_connect_error());
    }
    
    $bank = "SELECT SUM(payout) AS `totalpayout` FROM `finances`";
    $resultb = mysqli_query($conn, $bank);
    
    $startcash = "UPDATE finances SET `payout` = `payout` - `bank_withdraw`";
    $results = mysqli_query($conn, $startcash);
    
    $currentcash = "SELECT (SUM(bank_withdraw) - SUM(cash_spent)) AS `currentcash` FROM `finances`";
    $resultc = mysqli_query($conn, $currentcash);
    
    
    if (mysqli_num_rows($resultb) > 0){
        $bank = mysqli_fetch_assoc($resultb);
    }
    
    if (mysqli_num_rows($results) > 0){
        $startcash = mysqli_fetch_assoc($results);
    }
    
    if (mysqli_num_rows($resultc) > 0){
        $currentcash = mysqli_fetch_assoc($resultc);
    }

The HTML code that I have:

    <h1>Payout and Withdrawal</h1>
    <h2>TOTAL AMOUNT OF PAYOUTS: <?php echo $bank['totalpayout']; ?></h2>
    <h3>CASH AVAILABLE: <?php echo $currentcash['currentcash']; ?></h3>

    <form action="../scripts/updatefinances.php" method="post">
        <label for="payout">Payout:</label>
        <input type="number" id="outer" name="payout">

        <br>
        <button>Add Payout</button>
    </form>
    
    <br>
    <br>
    <br>
    
    <form action="../scripts/updatefinances.php" method="post">
        
        <label for="bank_withdraw">Withdrawal From Bank:</label>
        <input type="number" id="inter" name="bank_withdraw">

        <br>
        <button>Withdraw</button>
    </form>
    
    <br>
    <br>
    <br>
    
    <form action="../scripts/updatefinances.php" method="post">
        
        <label for="cash_spent">Cash Spent:</label>
        <input type="number" id="inter" name="cash_spent">

        <br>
        <button>Update Spent Total</button>
    </form>

updatefinances.php is shown below:

<?php

$payout = $_POST["payout"];
$withdraw = $_POST["bank_withdraw"];
$cashspent = $_POST["cash_spent"];

$host = "localhost";
$dbname = "***";
$username = "***";
$password = "***";

$conn = mysqli_connect($host, $username, $password, $dbname);

if (mysqli_connect_errno()) {
    die("Connection error: " . mysqli_connect_error());
}

$sql = "INSERT INTO finances (payout, bank_withdraw, cash_spent)
        VALUES ('$payout', '$withdraw', '$cashspent')";

$stmt = mysqli_stmt_init($conn);

if ( ! mysqli_stmt_prepare($stmt, $sql)){
    die(mysqli_error($conn));
}

mysqli_stmt_bind_param($stmt, "iii",
                       $payout,
                       $withdraw,
                       $cashspent
                       );

mysqli_stmt_execute($stmt);

echo "Successfully Added a Payout or Withdraw
      <br>
      Payout: $payout
      <br>
      Withdraw: $withdraw";
    
ckcDevRich
  • 29
  • 6
  • Are your users entitled for any privacy? In the meaning should someone else be able to watch their entire list of transactions or amend it at will? – Your Common Sense May 29 '22 at 05:31
  • This is a business based thing. I’m thinking that anyone with admin access will be able to view and edit. – ckcDevRich May 29 '22 at 05:33
  • FYI, here is your question title put in the google search, [How to stop update sql query on page refresh](https://www.google.com/search?q=How+to+stop+update+sql+query+on+page+refresh). You could have saved yourself quite a LOT of trouble by typing the only title without all that long and winding question body – Your Common Sense May 29 '22 at 05:34
  • No no, I don't mean the admin access. But literally anyone. – Your Common Sense May 29 '22 at 05:34
  • No, only admins should be able to access. Not everyone should have access to this. Also, regarding your other comment. I’ve tried the header refresh. It didn’t seem to work for me. – ckcDevRich May 29 '22 at 05:35
  • But why you're writing it the way that anyone can watch the entire list of transactions or even amend them? – Your Common Sense May 29 '22 at 05:37
  • I’m new to php and sql. I don’t know how to avoid that yet. I do have a session start and ensuring the user is logged in and has admin access enabled. But that question you posted does not work for me. Not sure why my question was closed. – ckcDevRich May 29 '22 at 05:39
  • By the way, this code doesn't even work. Are you sure this is the actual code that saves a transaction in the database? – Your Common Sense May 29 '22 at 05:40
  • @nazim I have a strong feeling that newbies shouldn't mingle with other people's money. Or should they? – Your Common Sense May 29 '22 at 05:41
  • Yeah it does. It uploads everything I need in the database. I just have to figure out how to get the queries working properly. – ckcDevRich May 29 '22 at 05:41
  • This code is "working" exactly the same way as it "uses a header redirect". Neither can be actually seen in the code provided. – Your Common Sense May 29 '22 at 05:47

0 Answers0