2. PHPRunner General questions
  3. How to insert total from detail table to master table field?
This topic is locked

How to insert total from detail table to master table field?

 		8/3/2009 5:46:29 PM
PHPRunner General questions
A
 Alanmelon author

As a PHP/MySQL newbie, I'd appreciate some help in setting up an event action in PHPrunner which will take the total of series of numeric entries in a detail (child) table and insert the value into a field in the corresponding master table.
The background to this is a sales database listing sales in the master table with separate individual payments recorded in the detail table, the total of which is then inserted into the master entry which calculates the balance.
Many thanks in advance!
J
 		Jane 8/4/2009

Hi,
use After record added event on the Events tab for this purpose.

Here is just a sample:

global $strTableName,$dal;

$rs = CustomQuery("select sum(FieldName) from DetailTableName where DetailKey=".$_SESSION[$strTableName."_masterkey1"]);

$data = db_fetch_numarray($rs);
$dal->MasterTableName->Value["TotalFieldName"] = $data[0];

$dal->MasterTableName->Param["MasterKey"] = $_SESSION[$strTableName."_masterkey1"];

$dal->MasterTableName->Update();



where DetailKey, MasterKey, FieldName and TotalFieldName are your actual field names, DetailTableName and MasterTableName are your actual table names.
A
 		Alanmelon author 8/4/2009

Fantastic - works perfectly! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=43233&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />