Triggers and account balances in mysq database

Greetings everyone. Please, see below, my tables structure.

TABLE 1: payment
PAYMETNTID bankname accountname accountnos acctype phone amountpaid datepd totalpaid
1 FBN paul bawam 00012198 current 080 50 0
2 FBN HOWAN 00012453 savings 080 100 0
3 FBN paul bawam 00012198 current 080 150 0

TABLE 2: balance
balanceid accountnos amountwtd newbalance date
1 00012198 30 0
2 00012453 60 0
2 00012198 90 0

from the 1 tables above, I want to calculate totapaid automatically for table “payment” .
table 2 is going to give balances after withdrawal.

ihave tried to use trigger below but still could not solve the problem:

DELIMITER $$
CREATE TRIGGER after_payment_update 
    AFTER UPDATE ON payment
    FOR EACH ROW 
BEGIN
    INSERT INTO payment
    SET action = 'update',
     bankname = OLD.bankname,
        accname = OLD.accname,
		accnos = OLD.accnos,
		acctype = OLD.acctype,
		phone = OLD.phone,
		amountpd = OLD.amountpd,
		datepaid = OLD.datepaid,
		totalpd = OLD.amountpd+,
		WHERE accnos='accnos';
         
END$$
DELIMITER ;

MY PHP CADE FOR WITHDRAWAL IS AS FOLLOWS:

//withdraw.php

<?php
if(isset($_POST['submit'])){
	$accnos=trim($_POST["accnos"]);
	$amount=trim($_POST["amount"]);
	
	
 /*$sql = "SELECT paymentid, accname, accnos, amountpd FROM payment WHERE accnos='$accnos'";
 $query = $dbh->prepare( $sql );
 $query->execute();
 $results = $query->fetchAll( PDO::FETCH_ASSOC );
 
 $amountpd = $_GET["amountpd"];	

 */
 
	 //$amountpd;=$row['amountpd']
 
 $sql = "SELECT paymentid, accname, amountpd, datepaid FROM payment WHERE accnos='$accnos'";
$result = $conn->query($sql);


if ($result->num_rows > 0) {
	$no 	= 1;
		//$total 	= 0;
		
		//$total += $amountpd;
			//$no++;
			
		
    // output data of each row
	?>
	<table class="data-table">
		
		<thead>
			<tr>
				<th>Account Name</th>
				<th>Account Number</th>
				<th>Total Cash Deposit</th>
				<th>Date Deposited</th>
				
			</tr>
		</thead>
		<tbody>
		
		<?php
    while($row = $result->fetch_assoc()) {
		/*$buffer = array();
		$vars;*/
		
 
 
 
echo '<tr>
					<td>'.$row['accname'].'</td>
					<td>'.$accnos.'</td>
					<td>'.$row['amountpd'].'</td>
					<td>'.$row['datepaid'] . '</td>
					
				</tr>';
				
				$amount=trim($_POST["amount"]);
		        $amountpd=$row["amountpd"];
				$wd += TRIM($_POST["amount"]);


			$total += $amountpd;
			//$no++;
			$newB = $total-$amount;
			if($amount>$newB)
		{
			
			Print '<script>alert("You do not have sufficient Funds.");;
			window.location.assign("viewwd.php")</script>';
			exit("You have insufficient funds!");
			//header("location: balance.php");
			
		}
		
			}?>
		
		</tbody>
		
	</table>
<?php


			echo "<br>";
			echo "Total Deposit To Date : " .$total .'<br>';
		echo "Amount Withdrawn : " .$amount .'<br>';
		echo "New Cash Balance To Date:";
		echo	number_format($newB) .'<br>';
		echo "total withdraw .$wd" .'<br>';

    }

} 

	
//echo "connected";
		

		
	if(isset($_POST['submit'])){
		$accnos = $_POST['accnos'];
		$amount = $_POST['amount'];
		

		$insert = $dbh->prepare("INSERT INTO balance(`accnos`,`amount`) values(:accnos, :amount)");
		$insert->bindParam (':accnos',$accnos);
		$insert->bindParam (':amount',$amount);
		//$insert->execute(array($seesion['accnos']));
		//$insert->execute(array($seesion['amount']));
		
		//(array($_SESSION['user']))
		
		
if ($insert->execute()) {
echo "<script type= 'text/javascript'>alert('The Amount of $amount is withdrawn from Account Number .$accnos Successfully');</script>";

}
else{
echo "<script type= 'text/javascript'>alert('Insufficent Funds. Withdrawal is not successfully .');</script>";
} }

?>

<html>
<head>
      <title>Withdraw Cash</title>
</head>
<body>
<h1>Withdraw Cash Here</h1>
<form action="withdraw.php"method="post">
<input type="text" name = "accnos" placeholder = "Account Number"> </br> </br>
<input type="text" name = "amount" placeholder = "Amount To Withdraw" </br> </br>
<input type="submit" name = "submit" Value = "Cashout Now">
</form>
</body>
</html>

deposit.php

<?php
ob_start();
	session_start();
	$error = false;
	
	require_once("config.php");

$accnosError=$accnameError=$acctypeErr=$acctypeError=$phoneError=$phoneError=$amountError=$nccodeError=$testifyError=$banknameError=$locationError=$emailError="";
	if ( isset($_POST['btn-signup']) ) {
	//define variables here

if( !$error ) {
			
			$query =mysql_query (" INSERT INTO payment( bankname, accname, accnos, acctype, phone, amountpd, location) VALUES('$bankname', '$accname', '$accnos', '$acctype',  '$phone', '$amountpd', '$location')") or die(mysql_error());
			$res = mysql_query($query);
				
			if (!$res) {
				$errTyp = "success";
				$errMSG = "<font color='green'>Payment Successfully Credited To <br><td>A/C Name: .$accname </td><td>A/C Nos:.$accnos</td> <td>Amount:.$amountpd</font></td>";
				unset($bankname);
				unset($accname);
				unset($accnos);
				unset($acctype);
				unset($phone);
				unset($amountpd);
				unset($location);
				
//echo <div id='div1'>Payment Successfully Credited To .$accname .$accnos .$amountpd</div>";
//UPDATE `payment` SET `totalpd` = '0' WHERE `payment`.`paymentid` = 3;				

			} else {
				$errTyp = "danger";
				$errMSG = "Something went wrong, try again later...";	
			}	
				
		}
		
		
	}
?>

i could insert and select successfully from the tables and display some calculated results.
But my challenge is to have values deducted from table “payment” on the instance of withdrawal and at the same time, update the table “balance” automatically with the amount withdrawn, giving accurate balances. I am stocked here. Can someone help me please. Thanks in advance.

Please, notice from the table “payment” above the possibility of one customer having multiple deposits, just as the same customer may have several accounts in the same bank or in different banks but can deposit and or withdraw centrally.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.