0

I have created a mysql table name 'members' where payment and ewallet are two columns... I want to make ewallet column such that it will automatically update when payment will update.. the rule will be ewallet=0.75*payment. How it is possible?

there is another column 'enroller_id' through which i am calculation the left count and the right count downline with the php function 

function getTotalLeg($node,$leg){
$sql="select enrolled_id from members where enroller_id='$node' and tside='$leg' and active='1'";

$res=mysql_query($sql);

global $total;

$total=$total+mysql_num_rows($res);
$row=mysql_fetch_array($res);

 if($row['enrolled_id']!=''){
   getTotalLeg ($row['enrolled_id'],0);
   getTotalLeg ($row['enrolled_id'],1);
  }

return $total;
}

Is it possible to count it in the database? Please help me in detail way. Thanks in advance.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Agniswar Chakraborty
  • 175
  • 1
  • 12
  • 1
    Have a look at [Triggers](http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html). In combination with [Stored Procedures](https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html) you are able to do quite interesting stuff :) – Tobias Golbs Feb 28 '16 at 09:59

1 Answers1

1

You could use generated columns (MySQL 5.7.6+):

Values of a generated column are computed from an expression included in the column definition.

Generated column definitions have this syntax:

col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [[NOT] NULL] [[PRIMARY] KEY]

CREATE TABLE members(
   ...,
   ewallet DECIMAL(14,4) AS (0.75 * payment)
);
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Two clarifications: it is not MySQL **5.7+** - it is **5.7.6+**. Source is the link you have provided to MySQL documentation(section "CREATE TABLE and Generated Columns"). Second - [this answer](http://stackoverflow.com/a/4708560/25429) contains some important points why similar use-case should not be implemented with `computed/calculated column`. – zloster Feb 28 '16 at 10:52
  • @zloster Yes, 5.7.6. Second part as always it depends. – Lukasz Szozda Feb 28 '16 at 10:57
  • Because of the "it depends" I've added my remark. We can't be sure if the asker have thought about the implications :) – zloster Feb 28 '16 at 11:18