array grouping

[adamohern]

I'm having trouble finding a good way of grouping a 2D array by a given column. Specifically, Lets say I've got the following array (written out longhand for easy reading):

$data['date'][0] = 2007-11-25;
$data['amount'][0] = 100;
$data['date'][1] = 2007-11-25;
$data['amount'][1] = 200;
$data['date'][2] = 2007-11-26;
$data['amount'][2] = 300;

I want to do an array_sum() (or similar) on all amounts that occur on the same date. In this case, I would want to get the sum total of all amounts that occur on 11-25: 300.

How can I achieve this with an array of indeterminate size? What if I've got seven different days that I want to group, so that I get the sum total amount per day? Any ideas?

Adam

[d40sithui]

interesting problem. did you ahve to query the database for to generate this array? if so, there may be something on the sql side you can do. otherwise, something lke this might work. note that the date is in string format.

<?
$data['date'][0] = "2007-11-25";
$data['amount'][0] = 100;
$data['date'][1] = "2007-11-25";
$data['amount'][1] = 200;
$data['date'][2] = "2007-11-26";
$data['amount'][2] = 300;
$data['date'][3] = "2007-11-26";
$data['amount'][3] = 300;
$data['date'][4] = "2007-11-25";
$data['amount'][4] = 150;
$total = 0;
$search = "2007-11-26"; //what date to search for
foreach(array_keys($data) as $key){
foreach(array_keys($data[$key]) as $subkey){
if($data[$key][$subkey] == $search){
$total+=$data['amount'][$subkey];
}
}
}
echo "<br>\$total:$total";
?>

[PHP_Chimp]

$date = '2007-11-25';
$c = count ($data['date']);
$total = 0;
for ($i=0; $i<$c; $i++){
if ($data['date'][$i] == $date){
$total += $data['amount'][$i];
}
}
echo '<br />'.$total;

He beet me to it...

[edited by: PHP_Chimp at 10:57 pm (utc) on Nov. 26, 2007]

[mooger35]

this will give you the sum for each date value.

$data['date'][0] = "2007-11-27";
$data['amount'][0] = 100;
$data['date'][1] = "2007-11-25";
$data['amount'][1] = 200;
$data['date'][2] = "2007-11-25";
$data['amount'][2] = 300;
$data['date'][3] = "2007-11-26";
$data['amount'][3] = 300;
$data['date'][4] = "2007-11-25";
$data['amount'][4] = 150;

$unique_date = array_unique($data['date']);
asort($unique_date);

foreach(array_values($unique_date) as $date){
$total = 0;
foreach($data['date'] as $key => $value){
if($value == $date) $total += $data['amount'][$key];
}
echo "The sum for $date = $total<br>\r\n";
}

[adamohern]

Fantastic, guys. Thanks so much!

It's true that I could do something on the SQL side to make this happen, but in the larger scheme of the script everything is cleaner if I can use arrays.

But tell me this, is there a way once I've done an SQL query = $handle to do another query on the same $handle? Basically a query of a query? If so, then I might rethink this a little...

[adamohern]

Ok guys, I'm no code-poet, but here's what I ended up with (that still isn't quite working):

function subdivideData($theData) {

mysql_data_seek($theData,0);

for ($x = 0 ; $x < mysql_num_rows($theData); $x++){
$row=mysql_fetch_array($theData);
$theDataArray['amounts'][]=$row['amount'];
$theDataArray['transactionDates'][]=strtotime($row['transactionDate']);
}

$keys = array_keys($theDataArray['amounts']);
array_multisort($theDataArray['transactionDates'],$keys,$theDataArray['amounts']);

if (count($theDataArray['transactionDates'])<=12){ $subdividedDataArray = $theDataArray; }
else if ($theDataArray['transactionDates'][0]>=time()-(60*60*24*7)){$subdividedDataArray['dateString'] = 'Y-m-d';}
else if ($theDataArray['transactionDates'][0]>=time()-(60*60*24*7*4)){$subdividedDataArray['dateString'] = 'Y-m-d';}
else if ($theDataArray['transactionDates'][0]>=time()-(60*60*24*7*52)){$subdividedDataArray['dateString'] = 'Y-m';}
else if ($theDataArray['transactionDates'][0]>=time()-(60*60*24*7*52*1.5)){$subdividedDataArray['dateString'] = 'Y-m';}
else if ($theDataArray['transactionDates'][0]>=time()-(60*60*24*7*52*3)){$subdividedDataArray['dateString'] = 'Y';}
else {$subdividedDataArray['dateString'] = 'Y';}

foreach($theDataArray['transactionDates'] as $date){$subdividedDataArray['dateStrings'][] = date($subdividedDataArray['dateString'],$date);}//works fine up to this point, but afterwards things get a little weird...

foreach($subdividedDataArray['dateStrings'] as $search){
foreach(array_keys($theDataArray) as $key){
foreach(array_keys($theDataArray[$key]) as $subkey){
if($theDataArray[$key][$subkey] == $search){
$total+=$theDataArray['amount'][$subkey];
}
}
}
$subdividedDataArray['amounts'][] = $total;
$subdividedDataArray['dateStrings'][] = $search;
}

return $subdividedDataArray;
}

I know that the first half of the script works fine, but I can't seem to configure your examples well enough to make them work :( Sorry to be a pain, can anybody see what I'm doing wrong?

[d40sithui]

what exactly isnt working? what results do you get? what does the $subDivideDataArray[] look like?
one thing i noticed is that you didnt initialize $total at every foreach interval. might not be culprit, but worth checking out.

<?
foreach($subdividedDataArray['dateStrings'] as $search){
$total = 0;
....
?>

[adamohern]

Found a solution to the second half:

for($i = 0 ; $i < count($theDataArray['transactionDates']) ; $i++){
$subdividedDataArray['transactionDates'][] = $theDataArray['transactionDates'][$i];
$subdividedDataArray['amounts'][] = $theDataArray['amounts'][$i];
}

foreach($subdividedDataArray['transactionDates'] as $date){
$subdividedDataArray['dateStrings'][] = date($subdividedDataArray['dateString'],$date);
}

$dateStrings = array_unique($subdividedDataArray['dateStrings']);
for($i = 0 ; $i < count($theDataArray['transactionDates']) ; $i++){
$ii = 0;
foreach($dateStrings as $dateString){
if ($dateString == date($subdividedDataArray['dateString'],$theDataArray['transactionDates'][$i])){
$totals[$dateString] += $theDataArray['amounts'][$i];
}
$ii++;
}
}

return $totals;
}