php - check for matching key value in multidimensional array if exists in new array update key value by adding the the number

Question

So I have an array that has multiple entries based on the same order (soId). what I need to do is combine the totals of all the orders that have the same soId into a new array. Here is an example of my array:

    array:3738 [▼
  0 => array:8 [▼
    "A" => "1/3/2021"
    "B" => 10556
    "C" => 6963
    "E" => "AMAZON - D"
    "K" => "0.00"
    "L" => "15.00"
    "M" => "-15.00"
    "Q" => "111-327***"
  ]
  1 => array:8 [▼
    "A" => "1/3/2021"
    "B" => 10556
    "C" => 6963
    "E" => "AMAZON - D"
    "K" => "218.98"
    "L" => "47.06"
    "M" => "171.92"
    "Q" => "111-327***"
  ]
  2 => array:8 [▼
    "A" => "1/3/2021"
    "soId" => 10556
    "C" => 6963
    "E" => "AMAZON - D"
    "K" => "0.00"
    "L" => "6.99"
    "M" => "-6.99"
    "Q" => "111-32707***"
  ]
  3 => array:8 [▼
    "A" => "1/3/2021"
    "B" => 10556
    "C" => 6963
    "E" => "AMAZON - D"
    "K" => "0.00"
    "L" => "25.00"
    "M" => "-25.00"
    "Q" => "111-3270792-5031461"
  ]

So that the matching soID rows would be combined in the new array and would look like this:

array:3738 [▼
  0 => array:8 [▼
    "invoice" => "1/3/2021"
    "soId" => 10556
    "invoiceId" => 6963
    "client" => "AMAZON - D"
    "salePrice" => "218.98"
    "unitCost" => "94.05"
    "margin" => "124.93"
    "extInvoice" => "111-327***"
  ]

Here is what I was trying but I cannot seem to detect the soId in the new array.

$sheetData = array();

foreach ($sheet as $row) {
            //check for matching key value in multidimensional array
            if (isset($sheetData['soId'])) { // This is not working skips to else
                  // This occurence found in the new array
                 // add+ the salePrice to the new array salePrice.
                // add+ the unitCost to the new array unitCost.
               // add+ the margin to the new array margin.


                }
                else {

                // first occurence of soId
                $sheetData[] = array(
                    'invoice' => $row['A'],
                    'soId' => $row['B'],
                    'invoiceId' => $row['C'],
                    'client' => $row['E'],
                    'salePrice' => $row['K'],
                    'unitCost' => $row['L'],
                    'margin' => $row['M'],
                    'extInvoice' => $row['Q']
                );

            }


        }

I am not sure how to accomplish both identifying the multiple occurrences of the soId in the new array (sheetData) or how I could if found combine the total of the three rows: salePrice, unitCost, and margin on the matching soId rom in the new array.

question from:https://stackoverflow.com/questions/66047471/check-for-matching-key-value-in-multidimensional-array-if-exists-in-new-array-up

Answer 1

The reason for the conditional statement evaluating to false has to do with the fact that you simply push a new array into $sheetData.

$sheetData looks something like the following in the process:

$sheetData = [
    0 => [
        'invoice' => 'some_value',
        'soId' => '1234',
        ....
    ]
];

Thus whenever the test for isset($sheetData['soId']) is evaluated, it evaluates to false. Because 'soId' is not directly set on $sheetData but on one of the nested arrays (in the example index 0).

So, what I personally would do or recommend is to create a small mapping. This mapping will be used to relate the unique identifier soId to an array index. Whenever we first encounter a new soId we will thus push it onto the result array, followed by mapping the soId to its index.

This allows us to access the array index in constant time and to update the respective sums as such.

Please see the following example:

$map = [];
$sheetData = [];

foreach ($sheet as $row) {
    $soId = $row['B'];

    // Since there seemed to be presence of thousand separated numbers
    // we remove the separation symbol and convert it to a floatval first.
    $salePrice = floatval(str_replace(',', '', $row['K']));
    $unitCost = floatval(str_replace(',', '', $row['L']));
    $margin = floatval(str_replace(',', '', $row['M']));

    if (!key_exists($soId, $map)) {
        // For simplicity let's rename the fields the first
        // time we encounter the soId.
        $row = array(
            'invoice' => $row['A'],
            'soId' => $soId,
            'invoiceId' => $row['C'],
            'client' => $row['E'],
            'salePrice' => $salePrice,
            'unitCost' => $unitCost,
            'margin' => $margin,
            'extInvoice' => $row['Q']
        );

        // Array push gives us back the number of items,
        // hence - 1 is the index in the result array.
        $index = array_push($sheetData, $row) - 1;
        // For constant time access we track where we
        // stored the soId in the sheetData array.
        $map[$soId] = $index;

        continue;
    }

    // Otherwise we just add the values to the running totals.
    $sheetData[$map[$soId]]['salePrice'] += $salePrice;
    $sheetData[$map[$soId]]['unitCost'] += $unitCost;
    $sheetData[$map[$soId]]['margin'] += $margin;
}

echo '<pre>';
print_r($sheetData);
echo '</pre>';

The output:

Array
(
    [0] => Array
        (
            [invoice] => 1/3/2021
            [soId] => 10556
            [invoiceId] => 6963
            [client] => AMAZON - D
            [salePrice] => 218.98
            [unitCost] => 94.05
            [margin] => 124.93
            [extInvoice] => 111-327***
        )
)