problem in adding interest rates to my account tables

[heshanm]

Hi,

I want to add interest based on daily basis to my account tables. This is my account tables.
shakthi(ID, account_type,full_name, balance, interest). There are another 3 tables which includes same fields.

The amount is credited and the balance is updated very well. But interest should be added daily. I think it is a good way add interest manually by running a query.

A manager should execute that query each day.The query i tried is,

<?php

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

function update_interest(){
       

$query="UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest`12/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE shakthi SET `balance`= (`balance`+`balance`*`interest`7/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE surathal SET `balance`= (`balance`+`balance`*`interest`14/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE abhimani_plus SET `balance`= (`balance`+`balance`*`interest`1/300)";
 mysql_query($query) or die(mysql_error());
}

?>

But i want to know how i connect to this into my tables??

Thanks,
Heshan

[hielo]

The amount is credited and the balance is updated very well. But interest should be added daily.

OK, then if the queries are working fine, then your problem is NOT really about "adding interest rates to your account tables", but rather, "how to auto-execute queries on a daily basis". If that is the case, you need to set up a "cron job". If you are with a web host, ask them what utility they have available to schedule a "cron job".

[diafol]

But i want to know how i connect to this into my tables??

What do you mean? You mean connect the code to a button or something?

[heshanm]

@ ardav, i have add_interest.php which includes this coding and " Add Interest" button.
If i clicks on this button i want interest to be added to each account.

@ hielo, yeah i got the problem of how to execute queries manually on a daily basis.Is it must to set up a "cron job". I do not know anything about that.Can u explain how should am i use it?

[hielo]

Basically you need to use a particular program on your server that in turn executes a certain program at a specific date/time OR at periodic intervals.

On a windows server, you have the "Task Scheduler". On a Unix/Linux box you would use the crontab or at command. Again, if you are with a webhost, find out from them what they have available for you.

http://www.linuxjournal.com/article/4087
http://www.ibm.com/developerworks/linux/library/l-job-scheduling.html
http://kevin.vanzonneveld.net/techblog/article/schedule_tasks_on_linux_using_crontab/

[heshanm]

I am using vista operating system. I run my task scheduler and give the path of my php page. I gave the path to add interest daily when the user logged in. But it did not worked...

Can anyone help me out.....

[hielo]

I run my task scheduler and give the path of my php page

It's not clear to me what you did. If I am not mistaken, currently you update it "manually" by typing the url in the browser directly and loading the page. IF that is correct, then in windows scheduler you just need schedule some browser that you don't use too often ex: IE to open every day at a specific time and have it open on a specific url. If you cannot figure out how to specify the url that should open, then configure the browser that you chose so that its default homepage is the url to your page.

[heshanm]

In my early query there should be a problem. The function update_interest() did not call in the program. can anyone show how to call that function?

<?php

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

function update_interest(){
       

$query="UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest`12/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE shakthi SET `balance`= (`balance`+`balance`*`interest`7/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE surathal SET `balance`= (`balance`+`balance`*`interest`14/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE abhimani_plus SET `balance`= (`balance`+`balance`*`interest`1/300)";
 mysql_query($query) or die(mysql_error());
}

?>

[diafol]

WRT the button you can use the onclick property to send you to the php page with the code which is followed by a header() to send you back (or to another page) once the code has run. There are other ways, for example, you could use Ajax, but that may be a bit pointless.

An even simpler option would be to have a link containing an image (your button):

<a href="addinterest.php" title="Add Interest"><img src="images/intbutton.png" alt="Add Interest" /></a>

[hielo]

. The function update_interest() did not call in the program. can anyone show how to call that function?

try:

<?php

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

function update_interest($conn){
       

$query="UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest`12/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE shakthi SET `balance`= (`balance`+`balance`*`interest`7/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE surathal SET `balance`= (`balance`+`balance`*`interest`14/3000)";
 mysql_query($query) or die(mysql_error());
 
$query="UPDATE abhimani_plus SET `balance`= (`balance`+`balance`*`interest`1/300)";
 mysql_query($query) or die(mysql_error());
}
update_interest($connect);
?>

[heshanm]

@ hielo,

Following error occurred in this line.

$query="UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest`12/3000)";

The error message is,

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 '12/3000)' at line 1

[hielo]

You currently have: UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest`NUMERATOR/DENOMINATOR) enclose the fractional portion in parentheses and include the "*" operator to the left of the open parenthesis: UPDATE savings_investment SET `balance`= (`balance`+`balance`*`interest` * (NUMERATOR/DENOMINATOR) ) You have this problem on all your update statements.

[heshanm]

code is right. but it is not working.

I assigned the task daily to execute at specific time period. It seems interest rates are not added. Can you suggest me a solution?

Thanks,

[hielo]

are you sure balance and interest are not zero? 0*anything=0

[heshanm]

yeah, my table structure is like this.

abhimani_plus (ID,account_type,full_name,balance,interest)

At the time of opening the account the balance and interest are set to be zero. Thereafter it should be added daily when an account balance is there...

[hielo]

At the time of opening the account the balance and interest are set to be zero.

Ok, then there you have it. If BEFORE the UPDATE statement your balance=0 OR interest=0, then that row will be updated but the result will be zero again. This is not a php bug. This is just the result of your formula.

I don't know what you are working on, but to put things into perspective, when you open a bank account, you must have a minimum amount to open that account.

If you are telling me that according to the DEFINITION of your DB Table balance and interest are set to zero, then I can understand that. But if you are telling me that after someone opens an account they are allowed a zero balance, then that is the root of your problem. Your formula will never update to anything beyond zero. You must enter a number greater than zero.

[heshanm]

Thanks a lot, i will check it....

[kiroshimasylvia]

I appreciate the concern which is been rose. The things need to be sorted out because it is about the individual but it can be with everyone. I like this particular article It gives me an additional input on the information around the world Thanks a lot and keep going with posting such information.
======================
Savings Rates