cash deposits and withdrawals in php

[heshan]

Hi guys,

 

I want to do some financial transactions in my php project. I want to deposit money and withdraw money from accounts. The related database tables that i have created for this purpose are,

 

savings_investmet(si_number, balance, interest, account_type)

shakthi(sh_number, balance, interest, account_type) Likewise there are 4 account types.

 

deposit_table(si_number, sh_number,deposit_amount, account_type)

withdrawal_table(si_number, sh_number,withdrawal_amount, account_type)

 

There are 2 forms regarding deposits and withdrawals to enter data.

 

In my transaction details form there are 3 fields namely;  Account Type, Account Number and Deposit Amount/Withdrawal Amount exist. If I click on submit button the relevant deposit or withdrawal should be updated in relevant tables.

 

I have find it difficult to make a link between these tables. Can anyone help me out.....

 

Thanks,

Heshan

 

 

 

[Psycho]

I'm really not following you. It seems you are making this more difficult than it needs to be. You should not have separate tables for deposits and withdrawals and then another to store the totals. You only need ONE table to store the transactions indicating the type of transaction and the amount (deposits would be positive while withdrawals would be negative).

 

Using that you could easily get the current total for the account and report on deposits and withdrawals independantly as needed.

 

Of course you would want to have a separate table to store the user/owner of the account which all of the transactions would relate back to.

[abdfahim]

I can understand separate tables for separate account type if you want to have separate auto increment number for each account type (if you can leave with a centralized auto increased account number irrespective of account type, you might reduce number of table to 1). But why you need two tables for deposit and withdraw? Have one table with following fields

 

transaction_table(account_number, transaction_type, amount, account_type)

 

Then, in transaction details form have same 4 inputs - account_number, transaction_type (deposite/withdraw), amount, account_type

 

Afterward, for each transaction, insert in transaction_table and update corresponding account_table for that respective account_number and account_type

 

And, as I said before, have one generic account_number field for all your account tables as you have distinguished tables already (no si_number, sh_number etc., rather may be a simple generic name ID)

[heshan]

Thanks for your replies. Now i made one common table called " transaction_type".

 

But how could this update to the relevant account type table. I have no idea about that. At the same time i should also include the interest rates as well. They are different from one account to another.

Thanks,

[Psycho]

What do the transactions have to do with updating the account table? Also, if the interest rate is differnent between accounts then it shoudl be included in the account table.

 

However, if there is a fixed number of account types, then the account types should be it's own table and referenced back to the accounts table. I have no idea exactly how your db should be structured, but here is an example of how I would do it.

 

TABLE: account_types
atype_id | atype_name       |  rate
------------------------------------
1        | Standard Savings | 1.2
2        | Money Market     | 3.5
3        | Checking         | 0

TABLE: customers
cust_id | fist_name | last_name
--------------------------------
4       | Bob       | Smith
8       | Jane      | Davis

TABLE: accounts
acct_id | cust_id | type_id | date_opened | date_closed
-------------------------------------------------------
21      | 4       | 1       |  4/23/2010  |
22      | 4       | 3       |  4/23/2010  | 
23      | 8       | 2       |  5/13/2010  | 

TABLE: transaction_types
ttype_id | ttype_name
----------------------
15       | Deposit
16       | Interest Payment
17       | ATM Withdrawal

TABLE: transactions
acct_id | ttype_id | date      | memo | amount
21      | 15       | 4/23/2010 |      | 250
21      | 15       | 5/6/2010  |      | -80

 

From the above you can see that Bob has two accounts: standard savings and checking. Also, he opened the savings account on 4/32 with a deposit of $250 and he has an ATM withdrawal on 5-6-2010 of $80

 

So, you could easlity find the current balance of any account by joining the applicable tables and doing a sum of the amounts column.

[abdfahim]

@mjdamato .. that's perfect structure for general case, but from heshan's last topic, as far as I understand, he wants to maintain a separate serial of A/C number of separate account types. For example, if BOB is the first person to create an account in Standard Savings and then another account in Checking, his customer ID will be 1 for both the account, just different account types. Then JANE create another account in Checking, and her account number will be 2 (not 3) of Checking account type.

 

But, if heshan can leave with a centralized serial number, then this is perfect structure.

[Psycho]

That scenario still works for the structure I provided and the structure I provided is better from a database perspective.

 

If I am followign what you are saying each "person" will have one account number - so just make the unique ID on the "customers" table the account number. You still want unique IDs in the accounts table to keep everything organized, but you don't need to expose that number to the user.

 

Plus, I have never been with a finincial institution that does nt have different acct number for each account type. But, oh well.

[abdfahim]

If I am followign what you are saying each "person" will have one account number -

 

Actually No .. .... I didn't mean a person will have one account number. I mean each account type will have it's separate queue. like below (these are NOT DB structure, I am just listing account ID, holder and date of opening for clarification)

 

saving_account
ac_id | ac_holder | Date of opening
---------------------------------------------
1        | BOB         | 1 June
2        | JANE        | 17 June
3        | MIKE        | 23 June

Current account
ac_id | ac_holder | Date of opening
---------------------------------------------
1        | Peter        | 5 June
2        | BOB          | 9 June
3        | Frank        | 3 July

 

I think in your structure, it'll be tough to implement, rather that will have a continuous account number irrespective of account type, like below, ryt (focus on ac_id)?

 

 

saving_account
ac_id | ac_holder | Date of opening
--------------------------------------------
1        | BOB         | 1 June
4        | JANE        | 17 June
5        | MIKE        | 23 June

Current account
ac_id | ac_holder | Date of opening
---------------------------------------------
2        | Peter        | 5 June
3        | BOB          | 9 June
6        | Frank        | 3 July

 

Anyway, Heshan can clarify what he wants.

[heshan]

@mjdamato, Your account structure has been perfect. But i do not want to go into that much of details. This is considering only internal transactions. ATM transactions done by the customers are not within my scope of this project. Anyway thanks for your comments.....

 

@abdbuet, I prepared my transaction table as what you recommend me to do. Now i do not want to change my other table structures. I have a problem of updating account type tables( savings_investment, shakthi, surathal etc.)

 

The interest rates should also be different from one account to another. I want the query?

 

Thanks,

[abdfahim]

tell me about your transaction structure. When anyone withdraw money, his balance should decrease from account table, right? What about interest? Does it have anything to do? I am not an accounting person, so you need to tell me in details.

 

To insert your new transaction in transaction table

 

transaction_table(account_number, transaction_type, amount, account_type)

 

$query = "INSERT INTO  transaction_table VALUES('".$_POST['account_number']."','".$_POST['transaction_type']."','".$_POST['amount']."','".$_POST['account_type']."')";

If you want to have date record, just add that info.

 

To add/deduct from the corresponding account table

if(strtolower($_POST['transaction_type'])=="deposit"){
$operator = "+";
}else{
$operator = "-";
}
$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount']") WHERE ID='".$_POST['account_number']."'";

 

I presume u change si_number, sh_number etc. to one unique name "ID"

[heshan]

@abdbuet, thanks for your codings.i will try it.

 

Interest should be added to the account in daily terms.

 

For an example if savings investment account interest is 5% monthly and the account balance is 900,

The daily interest should be = 900*(0.05/30)

                                              = 1.50

Then the balance should be updated as= 900+1.50 = 901.50

 

How this can be achieved......

 

Thanks,

[abdfahim]

hmm, as this is not a fixed deposit (where you can apply formula after certain period of time), interest will be added on the current balance (which is subject to change if anyone deposit/withdraw money), you need to calculate and add interest to the balance periodically (daily/weekly/monthly). Now the query is very simple, but execution need to be manual (that is you need to run the query periodically) or you have to have a Cron Job (like Windows scheduler) to run that query periodically.

 

Query will be something like for daily update

$query="UPDATE savings_investmet SET `balance`= (`balance`+`balance`*`interest`/300)"
//Repeat this for all the account_type tables

[heshan]

@abdbuet,

 

According to you this is the full coding..

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

if(isset($_POST['submit'])){

   $query = "INSERT INTO  transaction_table VALUES('".$_POST['account_number']."','".$_POST['transaction_type']."','".$_POST['amount']."','".                                                   $_POST['account_type']."')";
   if(strtolower($_POST['transaction_type'])=="deposit"){
$operator = "+";
}else{
$operator = "-";
}
$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount']") WHERE ID='".$_POST['account_number']."'";

$query="UPDATE savings_investmet SET `balance`= (`balance`+`balance`*`interest`/300)";
$query="UPDATE shakthi SET `balance`= (`balance`+`balance`*`interest`/300)";
$query="UPDATE surathal SET `balance`= (`balance`+`balance`*`interest`/300)";
$query="UPDATE abhimani_plus SET `balance`= (`balance`+`balance`*`interest`/300)";
}
?>

 

But it gives a parsing error on these lines.

$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount']") WHERE ID='".$_POST['account_number']."'";

 

Could you please look at the code again and see what are these errors?

 

Thanks,

[abdfahim]

cummon yaar, you need to execute the query for all ... after every "$query=...... " statement, run

mysql_query($query) or die(mysql_error());

[abdfahim]

oh, and for parsing error, one dot (.) is missing. Here is the corrected one

 

$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount'].") WHERE ID='".$_POST['account_number']."'";

[heshan]

Thanks and i will try it.

 

Can you explain this line in your coding.. what is the usage of "strtolower"

if(strtolower($_POST['transaction_type'])=="deposit"){

 

By the way, i want some addition features to be included. They are,

 

After entering all the information and click on submit button a relevant customer name should appear. It is must to verify the customer. I have a full_name field in my customer table. When opening accounts all the customer details have been stored in that table.How can it be linked up to this scenario?

 

And the other one is if user entered an invalid account number, a message should come as an invalid account number.

 

If all are correct there should be a message that saying " The transaction was successfully processed".

 

I also wanted to know whether it will be able to handle roll back operations in php. That is if a transaction fails due to a system error how can it be cancelled?

 

Thanks,

[abdfahim]

strtolower means string to lower .. that is converting the string to lower case. This is to make sure that no matter what you put in your form option value (Deposit or DEPOSIT etc.), it always convert it to small letter and then compare with the string (which I already typed as all small).

 

For your rest of the project, I'll try to reply you when I get my time (little bit busy with office work), in the mean time you can try yourself, and post your code here in case of difficulty. This will help your learning.

[heshan]

Thank you and hope you can able to attend this problem quickly as possible since it is very urgent for me....

 

But this query still generates an error.

$query = "INSERT INTO  transaction_table VALUES('".$_POST['account_number']."','".$_POST['transaction_type']."','".$_POST['amount']."','".                                                   $_POST['account_type']."')";

$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount'].") WHERE ID='".$_POST['account_number']."'";

 

It says,

 

Notice: Undefined index: amount in C:\wamp\www\php files\new_deposit.php on line 16

 

Notice: Undefined index: amount in C:\wamp\www\php files\new_deposit.php on line 23

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE ID='1'' at line 1

[abdfahim]

post here the output of

$query= "UPDATE `".$_POST['account_type']."` SET `balance`=(`balance`".$operator.$_POST['amount'].") WHERE `ID`='".$_POST['account_number']."'";
echo $query."<BR>";
mysql_query($query) or die('err2: '.mysql_error());

[heshan]

Here is the output...

 

Notice: Undefined index: amount in C:\wamp\www\php files\new_deposit.php on line 16

 

Notice: Undefined index: amount in C:\wamp\www\php files\new_deposit.php on line 24

UPDATE `surathal` SET `balance`=(`balance`+) WHERE `ID`='1'

err2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE `ID`='1'' at line 1

[abdfahim]

why don't you have anything in the variable $_POST['amount']? You have "amount" field in your form, ryt? As you dont have anything in $_POST['amount'], your SQL query returns error, "balance=balance+" this means nothing, right?

[heshan]

I entered an amount in my "amount" field in the cash deposit form. But it had not posted into the database.

[abdfahim]

can you paste your full code (including html portion) here?

[heshan]

Oops, sorry, i made a mistake in the form. Now it works...

 

Can you also give the answers for my additional features that i have previously posted..

 

Thanks,