Help need in deleting transactions

[heshan]

Hi,

 

I want to delete erroneous transactions and at the same time update my account table to be in line with the transaction table.

 

account (account_number, name_with_initials, accoount_type,account_balance,

account_interest)

 

transaction (tran_id,account_number,transaction_type,transaction_amount,transaction_date)

 

First i create a page to retrieve data from the tables and then need to delete the erroneous transaction.

 

Here is my coding

 

<?php

$connect=mysql_connect("localhost","root","");
mysql_select_db("bank",$connect) or die ("could not select database");

$account_number='';
$tran_id='';

if(isset($_POST['account_number']))
    $account_number=$_POST['account_number'];
else
    $account_number='';

//getting the faulty transaction
$query = "select * from transaction WHERE account_number='$account_number'";
mysql_query($query) or die(mysql_error());
//$row=mysql_fetch_assoc($query);

//Correcting the account table
if(strtolower('transaction_type')=="deposit"){
            $operator = "-";
      }else{
            $operator = "+";
      }
       $query= "UPDATE account SET `account_balance`=(`account_balance`".$operator.('transaction_amount').") WHERE account_number='$account_number'";
   
      mysql_query($query) or die(mysql_error());

//deleting the faulty transaction
$query = "DELETE from transaction WHERE account_number='$account_number'";
mysql_query($query) or die(mysql_error());

?>

 

There is an error message displays as

 

Unknown column 'transaction_amount' in 'field list'

 

It is because this field does not belong to account table. It is in the transaction table. But i could not find a solution to overcome the problem.Can anyone help me out..

[GKWelding]

<?php
$connect=mysql_connect("localhost","root","");
mysql_select_db("bank",$connect) or die ("could not select database");

$account_number='';
$tran_id='';

if(isset($_POST['account_number']))
    $account_number=$_POST['account_number'];
else
    $account_number='';

//getting the faulty transaction
$query = "select * from transaction WHERE account_number='$account_number'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);

//Correcting the account table
if(strtolower('transaction_type')=="deposit"){
    $operator = "-";
}else{
    $operator = "+";
}

$query= "UPDATE account SET `account_balance`=(`account_balance`".$operator.$row['transaction_amount'].") WHERE account_number='$account_number'";
   
mysql_query($query) or die(mysql_error());

//deleting the faulty transaction
$query = "DELETE from transaction WHERE account_number='$account_number'";
mysql_query($query) or die(mysql_error());

?>

 

Also, TBH, now you really should be using mysqli rather than mysql.

[heshan]

Did u change anything in my coding?

why i should use mysqli? Is it because it is an improved version of MySQL