2. PHPRunner General questions
  3. updating of detail summary on master field
This topic is locked

updating of detail summary on master field

 		1/26/2009 5:14:42 PM
PHPRunner General questions
D
 danaci author

dear friends ;
My Master table is

---------------------

M_number int autoinc

total int
My detail table is

-------------------

D_number int

price
relation is M_number = D_number
how to make update detail sum(price) on master table total field
My custom code is in after record added func.
global $conn,$strTableName;

$str = "select sum(price) from `detail` where D_number=".$_SESSION[$strTableName."_masterkey1"];

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$ttl=$data["price"];
$strUpdate = "update master set `total`=`total`+ ".$ttl." where sno =".$data["D_number"]."";

db_exec($strUpdate,$conn);
but don't any update on the master field total.
what is the regular code? please, thx.
A
 		alang 1/26/2009

Try:
global $conn;
$str = "select sum(price) from detail where D_number = '{$values["D_number"]}'";

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

$ttl=$data[0];

$strUpdate = "update master set `total`= '{$ttl}' where M_number = '{$values["D_number"]}'";

db_exec($strUpdate,$conn);
You should call the same code after an edit of the detail records too.
BTW, I would normally have an autoinc key field in the detail table as well, ie `DetailID`
D
 		danaci author 1/27/2009

much thnx Dear AlanG,

my problem is solved.