2. PHPRunner General questions
  3. [SOLVED]  working with totals
This topic is locked
[SOLVED]

 working with totals

 		6/20/2012 14:10:52
PHPRunner General questions
C
 cristi author

I have two tables in database with values :price1, price2, name, date and price3,price4, name, date

For price1 and price2 in the first table and price 3 and price 4 in the second table I have four totals field (total). How can I transfer this totals fields from the two tables into another table???
C
 		cgphp 6/21/2012

It doesn't look a phprunner related issue. Please, try to reformulate your question.
C
 		cristi author 6/21/2012

O.K.
I have a column "Price" in a table. For that column in the "totals" tab in phprunner I choose Totals type: Total.

How can I transfer that Total in another table???
C
 		cgphp 6/21/2012

Do you want to update another table?
C
 		cristi author 6/21/2012



Do you want to update another table?


The problem actually is a little more complicated...I don't know if it can be done with phprunner:
I have a table with some columns - Table1: column1, column2, PRICE1. For this table I need the sum of all rows with PRICE value. I can do this simple in phrunner using a "totals total" field. The second table has this composition - Table2: columnx, columny, PRICE2. I also need the sum of all rows values from the PRICE2 column.

The values from PRICE1 totals total field and PRICE2 totals total field must be automatically inserted in the third table Table3 with comuns: columna, columnb, columnc Total of PRICE1 and TOTAL of PRICE2. I also need in TABLE3 a sum between Total of PRICE1 and TOTAL of PRICE2 values.
Also, when I change something at the values of PRICE1 or PRICE2 in Table1 or Table2 I want this reflected (updated) automatically in Table3.
Can this be done in phprunner???
Sergey Kornilov admin 6/21/2012

You need to implement code that will update data in TABLE3 every time something changes in table1 or in table2. The best approach is to create a separate PHP function for this purpose and add a call to this function to all AfterAdd/AfterAdit/AfterDelete event of tables Table1/Table2.
I would also suggest to put this PHP function to external PHP file for convenience purposes. Here are instructions on including external PHP files:

http://xlinesoft.com/phprunner/docs/how_to_add_external_files.htm
C
 		cristi author 6/22/2012



You need to implement code that will update data in TABLE3 every time something changes in table1 or in table2. The best approach is to create a separate PHP function for this purpose and add a call to this function to all AfterAdd/AfterAdit/AfterDelete event of tables Table1/Table2.
I would also suggest to put this PHP function to external PHP file for convenience purposes. Here are instructions on including external PHP files:

http://xlinesoft.com/phprunner/docs/how_to_add_external_files.htm


Thank you for your suggestion.

I started to work and it's coming out nicely.
I already implemented the update/delete script based on a inner join between the two tables.
One more question: I need to update/delete some records in a table based on values inserted from another table. I want to do that without foreign keys in mysql because well... I can't.

So I want something like this: I create a ID column as primary key and auto increment in my source table and I want to insert different column values from this table + the ID value in another table. The problem is that because I don't write the ID - it is automatically added - I can't use a script like this:

global $conn;

$strSQLInsert = "insert into `Direct expenses` (`ID`,`Name`, `Travel expenses`) values ('".$values["ID"]."','".$values["Name"]."','".$values["Travel expenses"]."')";

db_exec($strSQLInsert,$conn);


Is there a way to insert also the ID (without being manually inserted of course) from my source table to another table?
I need to use this ID as a reference for update/delete operations from the source table to be reflected in the table where the values were automatically inserted.
C
 		cgphp 6/22/2012

In the "After record added" event, you have access to the array of key column values that point to the new record. If this is not your case, check this function: http://www.php.net/manual/en/mysqli.insert-id.php
C
 		cristi author 6/22/2012



In the "After record added" event, you have access to the array of key column values that point to the new record. If this is not your case, check this function: http://www.php.net/manual/en/mysqli.insert-id.php


Thank you!
The event is not working - it inserts the ID now but NOT the rest of the values...BUT your suggestion with mysqli.insert-id.php works beautifully!!!
EDIT: My mistake - also the after record added suggestion is working so double thank you!
C
 		cristi author 6/23/2012

One more question regarding working with ID as primary key:
I want to delete a record from another table based on the primary key ID from the first table.
I used a insert event to insert the primary key ID from first table in the second table as a normal value.
then I used in the list page before record deleted the following code:

$sql = "DELETE Total, `Working hours` FROM Total INNER JOIN `Empl`

WHERE Total.ID=`Empl`.ID";

$rs=CustomQuery($sql);


When I use this and I try to delete a selected record from the first table (Empl) the screen stays in loading and nothing happens...

Even using this in "after record deleted" in the first table gives the same result.
C
 		cgphp 6/23/2012

In the "Before record deleted" event, you can access the array with the field values from the record to be deleted. Check the user guide: http://xlinesoft.com/phprunner/docs/before_record_deleted.htm

global $conn;

$sql = "DELETE FROM Total WHERE ID = ".$deleted_values['ID'];

db_exec($sql,$conn);
C
 		cristi author 6/23/2012



In the "Before record deleted" event, you can access the array with the field values from the record to be deleted. Check the user guide: http://xlinesoft.com/phprunner/docs/before_record_deleted.htm

global $conn;

$sql = "DELETE FROM Total WHERE ID = ".$deleted_values['ID'];

db_exec($sql,$conn);



Thanks.

One more (final question) I promise.
Based on that model it means that if I modify a value (Total) in one table (Totalamount) and I want that modification to be updated in another table(Exp) - with updated value Amount - with the same ID used as reference I should use on the edit page before record updated the code:

global $conn;
$sql="UPDATE `Exp` SET Amount=".$values["Total"]." WHERE ID='".$values["ID"]."'";
db_exec($sql,$conn);


But it is not working...
C
 		cgphp 6/23/2012

If database fields are INT, remove single quote from the query:

global $conn;

$sql="UPDATE Exp SET Amount=".$values["Total"]." WHERE ID = ".$values["ID"];

db_exec($sql,$conn);
C
 		cristi author 6/23/2012



If database fields are INT, remove single quote from the query:

global $conn;

$sql="UPDATE Exp SET Amount=".$values["Total"]." WHERE ID = ".$values["ID"];

db_exec($sql,$conn);



It's not working...

ID is INT but amount and Total are double

After edit when I want to save nothing happens - the popup edit window don't execute
C
 		cgphp 6/23/2012

Check firebug for errors.
C
 		cgphp 6/23/2012

Try this version:

global $conn;

$sql="UPDATE Exp SET Amount=".$values["Total"]." WHERE ID = ".$keys["ID"];

db_exec($sql,$conn);
C
 		cristi author 6/23/2012



Try this version:

global $conn;

$sql="UPDATE Exp SET Amount=".$values["Total"]." WHERE ID = ".$keys["ID"];

db_exec($sql,$conn);



It's not working but there is something strange...
This Total value is autocalculated on the edit page using javascript onload event.
If I remove the javascript onload event and I enter a value for total then it's working but if the value is autocalculated from another two values in the edit page (which I modify at the edit time and so the total value is modified) then nothing happens...
C
 		cgphp 6/23/2012

Post the full code (javascript as well).
C
 		cristi author 6/23/2012



Post the full code (javascript as well).


Table 1:
SELECT

ID,

Worker,

App,

hours,

pay(per hour),

pay(per hour)*hours AS Total

FROM Totalamount
Table 2:
SELECT

ID,

Worker,

Amount,

Travel expenses,

ID1

FROM Exp
Totalamount ->Edit page->Before record updated

$sql="UPDATE Exp SET `Amount`=".$values["Total"]." WHERE ID = ".$keys["ID"];

db_exec($sql,$conn);


Totalamount ->Edit page->javascript onload event

var ctrlPrice = Runner.getControl(pageid, 'hours');
var ctrlQuantity = Runner.getControl(pageid, 'pay(per hour)');
var ctrlTotal = Runner.getControl(pageid, 'Total');

function func() {
if ( ctrlPrice.getValue()!='' && ctrlQuantity.getValue() && !isNaN(ctrlPrice.getValue()) && !isNaN(ctrlQuantity.getValue()))
ctrlTotal.setValue(parseFloat(ctrlPrice.getValue()) * parseFloat(ctrlQuantity.getValue()));
else
ctrlTotal.setValue('');
};

ctrlPrice.on('keyup', func);
ctrlQuantity.on('keyup', func);
C
 		cgphp 6/23/2012

Remember the global statement for the $conn:

global $conn;

$sql="UPDATE Exp SET `Amount`=".$values["Total"]." WHERE ID = ".$keys["ID"];

db_exec($sql,$conn);


Do you get errors in firebug?
C
 		cristi author 6/23/2012



Remember the global statement for the $conn:

global $conn;

$sql="UPDATE Exp SET `Amount`=".$values["Total"]." WHERE ID = ".$keys["ID"];

db_exec($sql,$conn);


Do you get errors in firebug?


Sorry - it is there -just got lost in the copy paste process.

Only some css errors and nothing else...
EDIT: Stupid me...because of the heatwave that is affecting the city my attention must be melted...I discovered where the error was: at first I calculated "Total" value as an sql query , then I used the javascript onload event but I forgot to delete the sql query - now Everything is working fine.
Thank you very much for your effort in fixing my problem.