subtotals within while loop

[toodi4]

I am running a simple query and while loop:

$query = "select * from table1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {

$plan_units = $row['plan_units'];
$plan_dollars = $row['plan_dollar'];
$division = $row['division];
$plan_units_total += $plan_units;
$plan_dollars_total += $plan_dollars;

}

The results display like:

division1 10 $40000
division1 30 $90000
division2 15 $52000
division2 18 $60000

Totals 73 $242000

What I'd like to do is get subtotals for each division. Now I know I could run a query for each division and simply run a total that way. But for various reasons, I'm trying to do this within one loop.

I'm currently using arrays that compare the current division name with the previous row's division name, so I know where the breaks are between divisions and I've set up the output table with sub-total rows in the right place.

I just can't figure out how to get a running total that reverts to zero at each break. I know this is probably simple, but I'm drawing a blank.

Thanks.

[n_wattam]

$division = $row['division];
$plan_units[$division]['plan_units'] = $row['plan_units'];
$plan_dollars[$division]['plan_dollar'] = $row['plan_dollar'];

but the above in your while loop, then out side use this

echo "<pre>";
print_r($plan_units);
echo "</pre>";

Have you thought about storing your results into a multidimentonal array?

Once you loaded the data from your Database you can then run a better quiery on it in a loop. and in turn create your sub totals better, or even still do it in the loop but use a milti dimention array instead.

to get an idea of what i am talking about if you've not used them

You should see more possibilities this way, i think the problem is the same with what i had, your working with 3d and thinking about it in a 2d method, instead of keeping it 3d, which multi dimention arrays help to do.

[toodi4]

is print_r the right function? I did it exactly as you suggested, including the <pre> tags, but it prints out the whole array.

[n_wattam]

yes thats right, the print_r and <pre> is to print the multidimentional array out on your screen so you can get an idea how it is structured.

what you should have is now an array for each 'division' set, and leaving the data in tact. where as before because your working with the same varibles you are replacing the data and moving on to the next loop.

you should be able to get a sub total for each 'division', and then you can do a subtotal using the multi dimention

With out having your data source and the coding, its hard for me to complete the script but the above should put you on the right track.

[n_wattam]

give me another 10mins, almost done the full code for you

[n_wattam]

ok this should work, i had to build the data source my self, and it works on that.

This is just my data source... you dont need this unless you want to test it with my script.

$division 								= 'division1';
$data[$division]['units']['value'][] 	= '10';
$data[$division]['dollar']['value'][] 	= '40000';

$division 								= 'division1';
$data[$division]['units']['value'][] 	= '30';
$data[$division]['dollar']['value'][] 	= '90000';

$division 								= 'division2';
$data[$division]['units']['value'][] 	= '15';
$data[$division]['dollar']['value'][] 	= '52000';

$division 								= 'division2';
$data[$division]['units']['value'][] 	= '18';
$data[$division]['dollar']['value'][] 	= '60000';

Your data source, with changes to how data is used...

$query = "select * from table1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {

$division 								= $row['division'];
$data[$division]['units']['value'][] 	= $row['plan_units'];
$data[$division]['dollar']['value'][] 	= $row['plan_dollar'];
}

foreach loop, to work data out

foreach ($data as $key_division => $value) { 
	foreach ($data['division1']['units']['value'] as $key_value => $value) { 

	$data[$key_division]['units']['total'] += $data[$key_division]['units']['value'][$key_value];
	$data[$key_division]['dollar']['total'] += $data[$key_division]['dollar']['value'][$key_value];

}

$subtotal['unit'] += $data[$key_division]['units']['total'];
$subtotal['dollar'] += $data[$key_division]['dollar']['total'];
}


print_r($data);
print_r($subtotal);