PHP do calculations with values from two arrays based on array element values

Question

Have two arrays

The first array

Array
(
[0] => Array
    (
        [SUM(j.Amount)] => 
        [DebitAccount] => 
    )

[1] => Array
    (
        [SUM(j.Amount)] => 15842.88
        [DebitAccount] => 2310
    )

[2] => Array
    (
        [SUM(j.Amount)] => 3656.68
        [DebitAccount] => 2380
    )

[3] => Array
    (
        [SUM(j.Amount)] => 12178.20
        [DebitAccount] => 2610
    )
 )

And the second array

Array
(
[0] => Array
    (
        [SUM(j.Amount)] => 
        [CreditAccount] => 
    )

[1] => Array
    (
        [SUM(j.Amount)] => 15842.88
        [CreditAccount] => 2310
    )

[2] => Array
    (
        [SUM(j.Amount)] => 30916.44
        [CreditAccount] => 2380
    )

[3] => Array
    (
        [SUM(j.Amount)] => 3133.70
        [CreditAccount] => 2620
    )
)

What is aim? Need to get such output

Account number is 2310, Value is 0.00 (15842.88 - 15842.88)

Account number is 2380, Value is -30916.40 (3656.68 - 30916.44)

Account number is 2610, Value is 12178.20 (12178.20 (DebitAccount) - 0.00 (CreditAccount ))

Account number is 2620, Value is -3133.70 (0.00 (DebitAccount) - 3133.70 (CreditAccount ))

Now trying to understand how to do something like that if(DebitAccount == CreditAccount){from (corresponding DebitAccount SUM(j.Amount)) deduct (corresponding CreditAccount SUM(j.Amount));}

Could you advice how to reach the aim?

I will try, but suppose then would not be understandable what I want to get.... — user2360831
– user2360831, Commented Jul 1, 2013 at 7:53
you need to do a double sum pass loop once with creditor details to get amount then loop a second time doing it in reverse for each debitor — Dave
– Dave, Commented Jul 1, 2013 at 7:54
Duplicate of your own question Sum php array (created from mysql results) depending on mysql values in another mysql column — C3roe
– C3roe, Commented Jul 1, 2013 at 8:12
Essentially it is an old style 2 file collate. Sort the 2 arrays into account number order, then you do a loop around. If they match you calculate the output value and go to the next items of both arrays, if one key is less than the other than the other then you have no matching value so deal with it as you need and proceed to the next item on the array with the lower key. However you mention SUM, which suggests that this is from a database. If so it might be easier to just do the calculation within the SQL. — Kickstart
– Kickstart, Commented Jul 1, 2013 at 8:26

Jacer Omri · Accepted Answer · 2013-07-01 08:59:59Z

1

let's say that the first array is $DAs and the second is $CAs

<?php
foreach($DAs as $i=>$da){
    foreach($CAs as $j=>$ca){
        if($da['DebitAccount'] == $ca['CreditAccount']){
            //output
            echo 'Account number is '.$da['DebitAccount'].', Value is '.$da['SUM(j.Amount)']-$ca['SUM(j.Amount)'].' ('.$da['SUM(j.Amount)'].' - '.$ca['SUM(j.Amount)'].')';
            //deduct the credit
            $DAs[$i]['SUM(j.Amount)'] -= $ca['SUM(j.Amount)'];
            //we need to use $DAs[$i] to be able to modify its value, because foreach can only give a copy of it.
        }
    }
}
?>

answered Jul 1, 2013 at 8:59

Jacer Omri

1,78322 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2360831 Over a year ago

Thanks, seems it works. Only need to add () like ($da['DebitAccount']) and ($da['SUM(j.Amount)']-$ca['SUM(j.Amount)'])

Kickstart Over a year ago

Down side of this is that if there is a record on $CAs with no matching record on $DAs then it will be completely ignored.

Jacer Omri Over a year ago

that's true, Kickstart, but he didn't say what to do in this case.

Kickstart · Accepted Answer · 2013-07-01 11:09:54Z

Coping with the arrays having different numbers of members needs something like this (not tested):-

<?php

usort($DAs, "cmpDA");
usort($CAs, "cmpCA");

$DaAccount = 0;
$CaAccount = 0;
$DaKey = 0;
$CaKey = 0;

while($DaAccount < 999999 OR $CaAccount < 999999)
{
    switch true
    {
        case $DAs[$DaKey]['DebitAccount'] == $CAs[$CaKey]['CreditAccount'] :
            echo "Account number is ".$DAs[$DaKey]['DebitAccount'].", Value is ".($CAs[$CaKey]['SUM(j.Amount)'] - $DAs[$DaKey]['SUM(j.Amount)'])." (".$CAs[$CaKey]['SUM(j.Amount)']." - ".$DAs[$DaKey]['SUM(j.Amount)'].")<br />";
            $DaKey = (($DaKey < count($DAs)) ? $DaKey + 1 : 999999);
            $CaKey = (($CaKey < count($DAs)) ? $CaKey + 1 : 999999);
            break;
        case $DAs[$DaKey]['DebitAccount'] < $CAs[$CaKey]['CreditAccount'] :
            echo "Account number is ".$DAs[$DaKey]['DebitAccount'].", Value is ".(0 - $DAs[$DaKey]['SUM(j.Amount)'])." (0 - ".$DAs[$DaKey]['SUM(j.Amount)'].")<br />";
            $DaKey = (($DaKey < count($DAs)) ? $DaKey + 1 : 999999);
            break;
        case $DAs[$DaKey]['DebitAccount'] > $CAs[$CaKey]['CreditAccount'] :
            echo "Account number is ".$CAs[$CaKey]['CreditAccount'].", Value is ".($CAs[$CaKey]['SUM(j.Amount)'] - 0)." (".$CAs[$CaKey]['SUM(j.Amount)']." - 0)<br />";
            $CaKey = (($CaKey < count($DAs)) ? $CaKey + 1 : 999999);
            break;
    }
}

function cmpDA($a, $b)
{
    return (($a['DebitAccount'] == $b['DebitAccount']) ? 0 : (($a['DebitAccount'] < $b['DebitAccount']) ? -1 : 1) );
}

function cmpCA($a, $b)
{
    return (($a['CreditAccount'] == $b['CreditAccount']) ? 0 : (($a['CreditAccount'] < $b['CreditAccount']) ? -1 : 1) );
}

?>

Sort the 2 arrays into the same key order. The do a while loop, while either array key isn't maxed out. When there keys match you can do the calculation. When one is bigger than the other then there is no matching record for the other so put it out an increment the array key to the other array. If it is the end of the array set the array key to something silly and large. Eventually both arrays will be completed and both keys set to something high.

However if the data is stored on a database then it is likely to be FAR quicker and easier to do this in SQL.

Collectives™ on Stack Overflow

PHP do calculations with values from two arrays based on array element values

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related