Aggravating aggregation

[ozymandius]

I have a PHP question for which I understand what I want to do but can't figure out how to do it.  Basically I'm just trying to figure out how to calculate subtotals from within a set of data.

 

I have a large set of data returned in an array from a search, which looks something like this:

 

       

     

028     

     

000264690     

     

1363.     

     

141     

         

       

     

028     

     

000264710     

     

4240.     

     

141     

         

       

     

006     

     

000265065     

     

87.     

     

141     

         

       

     

006     

     

000265083     

     

393.67     

     

141     

         

       

     

v113     

     

000265381     

     

148.     

     

141     

         

       

     

v113     

     

000265382     

     

52.     

     

141     

         

       

     

v113     

     

000265385     

     

75.     

     

141     

         

       

     

016     

     

000265482     

     

73.50     

     

141     

         

       

     

016     

     

000265483     

     

264.90     

     

141     

         

       

     

061     

     

000265656     

     

544.50     

     

141     

         

       

     

061     

     

000265663     

     

723.     

     

141     

         

       

     

008     

     

000265739     

     

3778.44     

     

141     

         

       

     

063     

     

000265870     

     

69.50     

     

141     

         

       

     

063     

     

000265946     

     

54.50     

     

141     

         

       

     

055     

     

000266060     

     

2316.43     

     

141     

         

       

     

055     

     

000266065     

     

1629.41     

     

141     

         

       

     

003     

     

000266137     

     

58.43     

     

141     

         

       

     

003     

     

000266139     

     

36.38     

     

141     

         

       

     

003     

     

000266363     

     

5908.55     

     

141     

         

       

     

003     

     

000266364     

     

3178.65     

     

141     

         

       

     

003     

     

000266373     

     

415.94     

     

141     

         

       

     

003     

     

000266497     

     

89.60     

     

141     

         

       

     

037     

     

000266613     

     

7855.60     

     

141     

         

       

     

037     

     

000266627     

     

513.92     

     

141     

         

       

     

037     

     

000266653     

     

-40.     

     

141     

         

       

     

037     

     

000266654     

     

-111.     

     

141     

         

       

     

v118     

     

000266714     

     

150.10     

     

141     

         

       

     

v105     

     

000266800     

     

2626.86     

     

141     

         

       

     

v105     

     

000266807     

     

2289.28     

     

141     

         

       

     

v125     

     

000266863     

     

24.75     

     

141     

         

       

     

v125     

     

000266869     

     

89.50     

     

141     

         

       

     

061     

     

000267120     

     

2272.40     

     

141     

         

       

     

061     

     

000267121     

     

164.16     

     

141     

         

       

     

061     

     

000267155     

     

2498.88     

     

141     

         

 

 

I can obviously add or remove any of these columns in the result if need be.  What I want to do is aggregate the third column (sales) by the first column (vendor)

 

I can easily get a total for everything but what  I really need is subtotals for each vendor number.

 

I've pursued doing a foreach loop where I'm looping through the array but I can't figure out how to make it summarize the sales data by the Vendor ID. 

 

Ultimately I want to put these subtotals into a string with their matching names so that I can append them to a specially formatted URL to send to the awesome google graphing service.

 

It will look something like this: http://chart.apis.google.com/chart?cht=p3&&chco=ff0000,00ff00,0000ff&chd=t:40,49,20,2,2,2,50,45&chs=450x225&chl=Avaya|Cisco|Nortel|Toshiba|NEC|Aastra|Scream|Tin%20Can

 

Any suggestions you have would be much appreciated.

 

I'm lost in the sea of PHP.

 

D

[Barand]

$totals = array();
foreach ($array as $sale)
{
     if (isset($totals[$sale[0]])
         $totals[$sale[0]] += $sale[2];
    else
         $totals[$sale[0]] = $sale[2];
}

[discomatt]

It might help a little if you told us which columns represent what data

[ozymandius]

Thanks for the replies so far.

 

Sorry if I was not clear.  I'm really just trying to understand the concept of aggregation.

 

In the table provided the first column is the vendor ID and the third column is the sales amount.  I could have left the other customer ID (second column) and saleseperson ID (fourth coulumn) out of the sample but I thought it might be important to show that the array as it is now contains other data besides that which I am trying to operate on.

 

Thanks again.

[ozymandius]

I'm new to PHP so please forgive the simplistic question.

 

in the example above

$totals = array();
foreach ($array as $sale)
{
     if (isset($totals[$sale[0]])
         $totals[$sale[0]] += $sale[2];
    else
         $totals[$sale[0]] = $sale[2];
}

 

My search results array is:  $SalesSearchResults_result['data']

 

Where do I insert it into the code?

 

I see the various array declarations but can't seem to make it work, and may be adding or leaving something off.

 

Or I guess I should say I'm SURE I'm adding or leaving something off.

[ozymandius]

Here is what one piece of the array looks like.  There is extra data in there but I'm just interested in ID_Vendor and TransactionAmount:

 [375913.2] => Array
        (
            [iD_InvoiceTransaction] => Array
                (
                    [0] => 000264683
                )

            [iD_Customer] => Array
                (
                    [0] => 00002673
                )

            [iD_Vendor] => Array
                (
                    [0] => v125
                )

            [iD_Sales] => Array
                (
                    [0] => 141
                )

            [TransactionAmount] => Array
                (
                    [0] => 376.
                )

            [Companies::_CompanyName] => Array
                (
                )

            [Vendors::VendorName] => Array
                (
                )

            [ASARoster::NameFull] => Array
                (
                )

            [DateofTransaction] => Array
                (
                    [0] => 02/01/2006
                )

            [RecDateCrted] => Array
                (
                    [0] => 03/29/2006 00:00:00
                )

        )

 

 

[Barand]

My solution asumed the array was as printed in first post, as you said it was an array

i.e.

$data = array (

    array (028,       	'000264690',       	1363.0,       	141),       
    array (028,       	'000264710',       	4240.0,       	141),      
    array (006,       	'000265065',       	87.0,       	 141),       
    array (006,       	'000265083',       	393.67,       	141),
    ...
);

 

which is what you'd get with a simple

 

$res = mysql_query("SELECT a,b,c,d FROM tablename");
while ($row = mysql_fetch_row($res))
{
    $data[] = $row;
}

     

[ozymandius]

actually this solution has a pre-existing filemaker database back-end, so the array returned is a bit verbose.  But the fxphp is pretty standard.  There actually are ways to use sql querries but that is not how this result is generated. 

 

Your foreach solution seems to make sense but I can't figure out how to implement it.  I'm learning PHP backwards. Have to do some catch-up study as soon as I can get some fires put out.

 

 

 

[ozymandius]

The array I'm showing in my later post is the result of dumping the array result from the search via:

<?php 
echo "<pre>";
print_r($SalesSearchResults_result['data']);
echo "</pre>";
?>