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";