Help need in deleting transactions

[heshanm]

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..

[faroukmuhammad]

From what I understand account_name is a primary key in account and a foreign key in transaction, therefore just because you delete a record in transaction doesn't mean a record in account most be updated. But when you delete a record in account, transaction has to be updated.

[diafol]

EEK! stop.

DELETE from transaction WHERE account_number='$account_number'

This will not just delete the faulty transaction, it will delete ALL transactions for that account.

I can't see what the heck you're trying to do here.

$query = "select * from transaction WHERE account_number='$account_number'";

Why are you selecting all transactions? You don't do anything with it. tran_id is initialised as ''?

Your error is coming from the SQL statement.

UPDATE account SET `account_balance`=(`account_balance`".$operator.('transaction_amount').") WHERE account_number='$account_number'

Where transaction_amount is not a field in account. You need to extract that 'amount' from the transactions table and place it in a variable before using it in this SQL.