[RESOLVED] subtotaling array

Kurtismonger

I have a database that tracks product defects. I can query the db and get a list of defects by part number for a particular department and date range. What I need to do is iterate through the returned query and count up the defects to get add up duplicate defect types and total defects by part number. I also need to sort the defect types by highest number of defects (for each part number) and display that in a table. Then I also want to count overall defect quantity. The same defect can occur multiple times for a given part number.

This is just example output, there could be up to 130+ types of defects for each part number.

This is the output of my query:
<PHP>
<TABLE cellpadding='0' cellspacing='0' border='0'>
<TR>
<TH>Dept</TH><TH>Part</TH><TH>Type</TH><TH>Defects</TH></TR>
<TR>
<TD>CNC</TD><TD>100</TD><TD>Handling</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>100</TD><TD>Scratches</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Supplier Mold Defect</TD><TD>2</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Handling</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Scratches</TD><TD>7</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Handling</TD><TD>6</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Scratches</TD><TD>3</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Base Run</TD><TD>4</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Scratches</TD><TD>6</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Supplier Mold Defect</TD><TD>9</TD></TR>
<TR>
<TD>CNC</TD><TD>101</TD><TD>Trimming</TD><TD>3</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Base Run</TD><TD>4</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Scratches</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Scratches</TD><TD>7</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Scratches</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Handling</TD><TD>1</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Scratches</TD><TD>3</TD></TR>
<TR>
<TD>CNC</TD><TD>102</TD><TD>Scratches</TD><TD>5</TD></TR>
<TR>
<TD>CNC</TD><TD>103</TD><TD>Scratches</TD><TD>13</TD></TR>
<TR>
<TD>CNC</TD><TD>103</TD><TD>Scratches</TD><TD>2</TD></TR>
<TR>
<TD>CNC</TD><TD>104</TD><TD>Scratches</TD><TD>10</TD></TR>
<TR>
<TD>CNC</TD><TD>105</TD><TD>Scratches</TD><TD>3</TD></TR>
<TR>
<TD>CNC</TD><TD>105</TD><TD>Trimming</TD><TD>1</TD></TR>
</TABLE>
<PHP>

So for example, For Part #103 I would want to sum the two entries for "Scratches" and print 15 and then provide a subtotal for #103 of 15. For part 105 I would return a count for scratches and one for Trimming and then a subtotal of 4.
I am really stuck here. Any help would be appreciated, even if its only a part of the solution. Not even sure I am asking this correctly, since I see a lot of reviews but not replies.

UPDATE:
Can't figure out why this is such an unusual problem that no one has seen before which is why I think I am asking the wrong question. I'll try to make it more generic:
I need to be able to walk through an array and sum and subtotal the elements in it without knowing what elements are going to be in the array beforehand. The array has the form:

part#/defectType/defectQty

As I stated above, the array could have multiple entries for each part number and there could by multiples of the same defectType and Part#.

101/scratches/5
101/scratches/3
101/Trim/19
102/scratches/12
102/Trim/3

For each part I want to subtotal defectTypes.

paulnaj

You could do something like this:
- Create a $totals array.
- For each row ...
- - check if $totals[$part] exists ...
- - ... if it does, do nothing
- - ... else create it as an empty array
- - check if $totals[$part][$type] exists ...
- - ... if it does, add $defects to it
- - ... if not create it equal to $defects

$totals[103]['Scratches'] will then hold the number of scratches for part 103.

Kurtismonger

Okay that does look like its on the right track - so how do I do that?
Here is what I tried and I just end up with nothing there

$totals = array();
while ($row = mysql_fetch_array($dbResult, MYSQL_ASSOC))
{
if(in_array($row->Part, $totals))
{
}
else
{
array_push($totals, $row->Part);
}
print("<BR/>\n");
}

paulnaj

Hi there,

First chance to get back to you ...

<?php
$totals = array();
while($row = mysql_fetch_array($dbResult, MYSQL_ASSOC)){
	if(!isset($totals[$row['Part']])){
		// Create array to store part info
		$totals[$row['Part']] = array();
	}
	if(!isset($totals[$row['Part']][$row['Type']])){
		// Create running total of defects per type and set to zero
		$totals[$row['Part']][$row['Type']] = 0;
	} else {
		// Add the number of defects (per type) to running total
		$totals[$row['Part']][$row['Type']] += $row['Defects'];
	}
}
?>

Kurtismonger

paulnaj - I got pulled off onto something else and just got back to this. Thank you for the followup! That's exactly what I needed and I think I've got a handle on finishing what I need it to do. With a few tweaks I can see the array is populating correctly with a print_r. Now I just need to do some sorting and format it for output. Thanks again for helping a newb.