Need some suggestions regarding executing a query at a specific date and time

[heshan]

Hi,

 

I want to add interest to my accounts @ the end of each month. For that i think i have to execute a query @ a specific date. Only the manager should be able to run the query. Interest rates are different based on account types. This is my account table.

 

account (account_number, account_type, account_balance, account_interest )

 

I want some suggestions regarding this issue. I have posted a sample coding that have been created to do this task.

 

<?php

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

function update_interest($connect){
       

if(account_type=='abhimani_plus'){
$query="UPDATE account SET `account_balance`= (`account_balance`+`account_balance`*`account_interest`*(1/3000))";
}
mysql_query($query) or die(mysql_error());

if(account_type=='shakthi'){ 
$query="UPDATE account SET `account_balance`= (`account_balance`+`account_balance`*`account_interest`*(7/3000))";
}
mysql_query($query) or die(mysql_error());

if(account_type=='savings_investment'){ 
$query="UPDATE account SET `account_balance`= (`account_balance`+`account_balance`*`account_interest`*(12/3000))";
}
mysql_query($query) or die(mysql_error());

if(account_type=='surathal'){ 
$query="UPDATE account SET `account_balance`= (`account_balance`+`account_balance`*`account_interest`*(14/3000))";
}
mysql_query($query) or die(mysql_error());
  
if(account_type=='yasasa'){ 
$query="UPDATE account SET `account_balance`= (`account_balance`+`account_balance`*`account_interest`*(20/3000))";
}
mysql_query($query) or die(mysql_error());
   
}
update_interest($connect);
?>

[Psycho]

Looking at your sample code it looks like the account type is used as a modifier for determining the interest. I would suggest creating an separate table to associate account types with the "modifier". Then you do not need to do all those IF statements and you can calculate ALL the interest changes in ONE query.

 

Such as

UPDATE account
SET `account_balance`=
    (
        `account_balance`+`account_balance`*`account_interest`*
            (
                                          SELECT modifier
                                          FROM modifiers
                                         WHERE modifiers.account_type=account.account_type
            )
    )

 

But, you are doing this wrong. You shouldn't be simply adding the interest to the account balance. Heck, you shouldn't have an account balance at all. You should have a table to track transactions. You would then calculate the account balance using the sum of the transactions. Then you can run the script to calculate the interest on the last day of the month or even several days after the end of the month and you could still calculate the correct interest based upon their account balance as of midnight on the last day of the month.

[heshan]

Thanks for your suggestions.

 

Could you pls tell me what are the field i should include in my "modifiers" table?

 

I have a seperate transaction table. Whenever a customer make a transaction it will get updated in my transaction table.

 

 

[Psycho]

Could you pls tell me what are the field i should include in my "modifiers" table?

You just need a column for the account_type and another for the "modifier" or whatever makes sense for you. Looking at your sample code, the modifier for the 'abhimani_plus' account type would be (1/3000) or whatever that mathematical result is.

 

I have a seperate transaction table. Whenever a customer make a transaction it will get updated in my transaction table.

So, why do you need an account balance field? You should be able to determine the account balance at any time by using the transactions. And, then you would just add the interest to the transactions table. Using your logic of updating the value of account balance with the interest amount how does anyone ever validate that the interest was applied since you would have no record of it?

[heshan]

Thnaks.... , now how am i going to execute my query. In manager's page i have created "Add interest" button.

 

When i click the button i want to add interests into these accounts.

 

I have created my modifiers table and insert values.

 

account_type               modifier

abhimani_plus                 1/3000

shakthi                      7/3000

savings_investment 12/3000

surathal                         14/3000

yasasa                         20/3000

[Psycho]

Didn't I provide a sample query above? If you saved the value as a text value instead of a numeric value, that query won't work though.

 

But, in my opinion, that is still the wrong way to do this. I would suggest to instead add a new record to the transactions table for the interest. But, that obviously would require a complete overhaul of what you have now.

[xyph]

I believe there is some value in keeping a running total.

 

This varies though. If a user is making several transactions a day, after a year, the query to find his balance would have to sum well over a thousand rows. If this balance has to be output often, that could turn into a bottleneck.

 

You could always keep the running total beside each transaction. A withdraw query could look like

 

INSERT INTO `transactions` (`account`,`date`,`type`,`amount`,`balance`)
VALUES ( 1, NOW(), 2, 100.00, 
( SELECT `balance` FROM `transactions` `t` WHERE `account` = 1 ORDER BY `date` DESC LIMIT 1 ) - 100.00
)

 

Using a table like

 

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `type` enum('Deposit','Withdraw') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `balance` decimal(10,2) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `transactions` (`id`, `account`, `date`, `type`, `amount`, `balance`) VALUES
(1, 1, '2011-08-19 09:51:22', 'Deposit', '1000.00', '1000.00'),
(2, 1, '2011-08-19 10:00:59', 'Withdraw', '50.00', '950.00'),
(3, 1, '2011-08-19 10:01:35', 'Deposit', '100.00', '1050.00');

[heshan]

It's work ....Thanks for your valuable ideas.....