Error in my PHP coding

[heshan]

The 3 tables are as follows.

 

account_details (account_number, nic, full_name,__)

 

account (account_number, account_type, account_balance, __)

 

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

 

The below coding does not work. It ONLY displays "invalid account number " for all types of data.

The other queries were not working. Can anyone help me out..

 

<?php

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

if(isset($_POST['submit'])){
$query = "SELECT account_details.full_name, account.account_balance ".
"FROM account_details, account ".
"WHERE account_details.account_number = account.account_number";
      $result = mysql_query($query) or die(mysql_error());
  
      $row = mysql_fetch_assoc($result);
  
      if(mysql_num_rows($result)==1 and $row['account_balance']<$_POST['transaction_amount'] and strtolower($_POST['transaction_type'])=="withdrawal"){
          echo "Insufficient balance";
      }elseif(mysql_num_rows($result)==1){

      if(strtolower($_POST['transaction_type'])=="deposit"){
    $operator = "+";
      }else{
            $operator = "-";
     }
$query= "UPDATE account SET `account_balance`=(`account_balance`".$operator.$_POST['transaction_amount'].")
               WHERE `account_number`='".$_POST['account_number']."'";      
      mysql_query($query) or die(mysql_error());
   
      $query = "INSERT INTO  transaction (transaction_type, transaction_amount, transaction_date) 
            VALUES('".$_POST['transaction_type']."','".$_POST['transaction_amount']."','".$_POST['transaction_date']."')";
  
mysql_query($query) or die(mysql_error());
            echo $row['full_name'].",<br> your transaction has been successfully processed";
      }else{
            echo "invalid account number";
      }
}

?>

[TeNDoLLA]

Apparently your if-conditions returns false and it goes always to the else part. You could debug the variables inside your if's to see if they are what they should be with var_dump($variable). You can also dump the whole if condition as a whole and see what it returns.

[Maq]

I think you're joining the table incorrectly and getting multiple records in your result set.

echo mysql_num_rows($result), I bet it's greater than 1.

 

 

[heshan]

@ Maq, year it is greater than 1. There were many records exist in "account" table. How can i modify the code?

 

@ TeNDoLLa, Could you give me an example in this coding?

[Maq]

Try something like:

SELECT ad.full_name, a.account_balance FROM account_details ad LEFT JOIN account a ON ad.account_number = a.account_number

[mikosiko]

@Maq... that has the potential to return more than one row anyways.... LEFT JOIN doesn't solve the problem.

 

I see a couple issues here:

- First, apparently the OP is capturing transactions associated to one (or several) accounts identified by an account_number, however he is not using the POST'ed account number (if any)  for his first select which should allow him to get just one record in the first select.

- Secondly, in the Transaction INSERT again he is not using the account_number, therefore is inserting orphan transactions not associated to any account.

 

my suggestions are

- Check for the proper POST'ed account_number usage in all the sentences.

- and finally, due to the fact that the objectives are: UPDATE the account balance and also register (INSERT) the associated supporting transaction(s) is necessary to ensure that BOTH actions succeed;  a TRANSACTION should be used in this case where you can COMMIT if both actions (INSERT/UPDATE) are successful or ROLLBACK everything is any of them fail.

 

for TRANSACTION usage here is an example that can be useful:

http://www.techrepublic.com/article/implement-mysql-based-transactions-with-a-new-set-of-php-extensions/6085922

[heshan]

@mikosiko; Thanks for the link. But i still not got it accurately.

 

Can anyone change my coding in a which i would be able to work on....