This topic is locked

getting totals from sub-tables

10/20/2008 10:57:05 PM
PHPRunner General questions
W
wfcentral author

I have a database like this...
tbl_CONTAINER

id, weight_total, pieces_total
tbl_PIECE

id, name, weight, pieces
So, think of the container as a box that holds 15 egg crates... inside each egg crate there may be anywhere from 1-12 eggs.
So, I might have
CONTAINER 1

  • PIECE1 = Egg crate 1, 5lbs, 6 eggs
  • PIECE2 = Egg crate 2, 8lbs, 9 eggs
    So, I have linked together tbl_CONTAINER as master to tbl_PIECE
    Now, on the list page for tbl_CONTAINER I would like to have all the totals from the PIECES load into the "weight_total" and "pieces_total" of the tbl_CONTAINER
    So, in the example above... container 1 should have total weight_total = 13lb and pieces_total = 15

T
thesofa 10/21/2008

OK, I have an orders database where I wanted to do a similar thing

each order has several lines, each line has one item but it may be a quantity of 50 and a unit price, so in the Edit SQL query page i have a field added to the `tlin` table of `line total` and in the Column column, I have

number_ordered * unit_price AS `'Line Total'`,



then on the `tord` table sql query I have

SUM(tlin.number_ordered * tlin.unit_price) AS `Goods Total`


because I am now working in the `tord` table, I have had to add the `tlin.` prefix to the field names in the formula.

You will also need to make the join between CONTAINER and PIECE into a left outer join to pick up the containers without any pieces in it
Translating it for your case, in the SQL query for the containers, you would have

SELECT

id,

SUM(tbl_PIECE.weight) AS `Total Weight`,

SUM(tbl_PIECE.pieces) AS `Pieces Total`

from tbl_CONTAINER

Left Outer Join tbl_PIECE ON tbl_CONTAINER.id = tbl_PIECE.id

GROUP BY tbl_CONTAINER.id


This means you do not need to save the total numbers and total weight each time you change the quantity it will update itself.
BUT, if you really want to save the values, on the After record added event page for the tbl_PIECE table, use the Custom event to add the values to the tbl_CONTAINER record

global $conn;

$id=$values["id"];

$no=$values["pieces"];

$wt=$values["weight"];

$sql="update tbl_CONTAINER set `weight_total`=`weight_total`+ ".$wt.",`pieces_total`=`pieces_total`+ ".$no." where (`tbl_CONTAINER.id`=".$id.");";

db_exec($sql,$conn);


HTH