Auto calculation
Posted: Sat Dec 04, 2010 2:43 am
Hi there
I want to make a cash transaction record (suppose as bank statement) by php and mysql.
Suppose I have the following data in a mysql table:
-----------------------------------------------------------------------------------------------
id | ----date--- | particular | --form-- | -debit- | -credit- |comsn| balance
====================================================================
1 | 13/06/2010 | Previous | ---------- | ------- | --------- | ------- | 3534.89
-----------------------------------------------------------------------------------------------
2 | 13/06/2010 | -deposit- | --cash-- | ------- | 82500.0 | 412.5 | 85622.39
-----------------------------------------------------------------------------------------------
3 | 14/06/2010 | withdraw | -cheque | 26850 | --------- | 134.5 | 58638.14
-----------------------------------------------------------------------------------------------
4 | 14/06/2010 | -deposit- | --cash-- | ------- | 20387.5 | 101.9 | 78923.7
-----------------------------------------------------------------------------------------------
5 | 15/06/2010 | -deposit- | --cash-- | ------- | 20400.0 | 102.0 | 99221.7
-----------------------------------------------------------------------------------------------
6 | 16/06/2010 | withdraw | -cheque | 22660 | --------- | 113.3 | 76448.4
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
problem is, suppose I have to delete row#3 for some reason at later. Then I have to do a complete update query for the whole table or atleast from row#3 to onward for correcting the value of BALANCE column because
BALANCE of row#4 = BALANCE of row#3 + CREDIT of row#4 - DEBIT of row#4 - COMSN (commision) of row#4
so after deletion of row#3, calculation of column#4 will be with row#2 instead with row#3 and then value of column#5 also will be changed and so on up to the last entered row of the table.
Thus I have to process a -
$row2 = mysql_query("SELECT balance FROM register WHERE id=2");
$row4 = mysql_query("SELECT credit,debit,comsn FROM register WHERE id=4");
$newbalance = $row2['balance'] + $row4['credit'] - $row4['debit'] - $row4['comsn'];
mysql_query("UPDATE register SET balance='$newbalance' WHERE id='4';");
and thereafter for each row after row#4.
It seems that this is a simple task for mysql. But think of a table register which has more than 20000 rows. then it becomes completed and creates overload for the mysql database. Moreover, if I have 2000 customers and all are trying to delete/insert/edit the table at the same time, then it will create a serious load on the mysql.
I want to know if there is any system so that I can link the columns inside mysql table (like we do in excel with a formula) so that if someone delete an interim row, calculation of BALANCE column for next rows will be done internally in the mysql table for which I don't have to run 3 queries ( 2 SELECT and 1 UPDATE) for correcting balance column in each row?
or there is some aggregate function to do the above query for each row in a single query?
Any help will be appreciated.
I want to make a cash transaction record (suppose as bank statement) by php and mysql.
Suppose I have the following data in a mysql table:
-----------------------------------------------------------------------------------------------
id | ----date--- | particular | --form-- | -debit- | -credit- |comsn| balance
====================================================================
1 | 13/06/2010 | Previous | ---------- | ------- | --------- | ------- | 3534.89
-----------------------------------------------------------------------------------------------
2 | 13/06/2010 | -deposit- | --cash-- | ------- | 82500.0 | 412.5 | 85622.39
-----------------------------------------------------------------------------------------------
3 | 14/06/2010 | withdraw | -cheque | 26850 | --------- | 134.5 | 58638.14
-----------------------------------------------------------------------------------------------
4 | 14/06/2010 | -deposit- | --cash-- | ------- | 20387.5 | 101.9 | 78923.7
-----------------------------------------------------------------------------------------------
5 | 15/06/2010 | -deposit- | --cash-- | ------- | 20400.0 | 102.0 | 99221.7
-----------------------------------------------------------------------------------------------
6 | 16/06/2010 | withdraw | -cheque | 22660 | --------- | 113.3 | 76448.4
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
problem is, suppose I have to delete row#3 for some reason at later. Then I have to do a complete update query for the whole table or atleast from row#3 to onward for correcting the value of BALANCE column because
BALANCE of row#4 = BALANCE of row#3 + CREDIT of row#4 - DEBIT of row#4 - COMSN (commision) of row#4
so after deletion of row#3, calculation of column#4 will be with row#2 instead with row#3 and then value of column#5 also will be changed and so on up to the last entered row of the table.
Thus I have to process a -
$row2 = mysql_query("SELECT balance FROM register WHERE id=2");
$row4 = mysql_query("SELECT credit,debit,comsn FROM register WHERE id=4");
$newbalance = $row2['balance'] + $row4['credit'] - $row4['debit'] - $row4['comsn'];
mysql_query("UPDATE register SET balance='$newbalance' WHERE id='4';");
and thereafter for each row after row#4.
It seems that this is a simple task for mysql. But think of a table register which has more than 20000 rows. then it becomes completed and creates overload for the mysql database. Moreover, if I have 2000 customers and all are trying to delete/insert/edit the table at the same time, then it will create a serious load on the mysql.
I want to know if there is any system so that I can link the columns inside mysql table (like we do in excel with a formula) so that if someone delete an interim row, calculation of BALANCE column for next rows will be done internally in the mysql table for which I don't have to run 3 queries ( 2 SELECT and 1 UPDATE) for correcting balance column in each row?
or there is some aggregate function to do the above query for each row in a single query?
Any help will be appreciated.