Sum within a foreach loop based on array with different group

Question

I'm trying to take successful array results and start doing calculations on values based on another value in the array.

To start with, I want to sum every quantity 'TotalQTY' for each customer 'CSTNOC' in these arrays.

The array is printing fine but I'm getting undefined index errors in my last foreach loop.

I feel like this is simple enough but I'm not sure if my array isn't structure properly of if it's an issue with my loop.

Array
(
[0] => Array
    (
        [CSTNOC] => 1976
        [FRAMEC] => 1051
        [COVR1C] => 1150
        [COLR1C] => 99
        [START_DATE] => 2018-03-02
        [TOTALQTY] => 2
    )

[1] => Array
    (
        [CSTNOC] => 5400
        [FRAMEC] => 1051
        [COVR1C] => 1150
        [COLR1C] => 99
        [START_DATE] => 2017-11-10
        [TOTALQTY] => 1
    )

[2] => Array
    (
        [CSTNOC] => 5400
        [FRAMEC] => 1051
        [COVR1C] => 1150
        [COLR1C] => 99
        [START_DATE] => 2017-04-07
        [TOTALQTY] => 2
    )

[3] => Array
    (
        [CSTNOC] => 5400
        [FRAMEC] => 1051
        [COVR1C] => 1150
        [COLR1C] => 99
        [START_DATE] => 2018-02-09
        [TOTALQTY] => 2
    )

[4] => Array
    (
        [CSTNOC] => 11316
        [FRAMEC] => 1051
        [COVR1C] => 1150
        [COLR1C] => 99
        [START_DATE] => 2017-03-03
        [TOTALQTY] => 1
    )

I basically want these results for an excel report purpose:

CSTNOC    |    TotalQTY
1976      |        2
5400      |        5
11316     |        1

This is the portion of the script:

$dealerQuery = "
        SELECT
              cstnoc,
              framec,
              covr1c,
              colr1c,
              cast(Left(extd2d, 4)||'-'||substring(extd2d,5,2)||'-'||substring(extd2d, 7,2) as date) as start_date,
              sum(orqtyc) as TotalQTY
          from table
            where cstnoc = {$skuRow['dealer_id']}
            AND framec = {$skuRow['frame']}
              AND colr1c = {$skuRow['color']}
              AND covr1c =  {$skuRow['cover']}
              AND extd2d >= " . str_replace('-', '', $skuRow['start_date']) . "
        group by cstnoc, framec,covr1c,colr1c,extd2d
    ";

    $dealerRslt = odbc_exec($DB2Conn, $dealerQuery);

    foreach($skuResult as $skuRow){
        while($dealerRow = odbc_fetch_array($dealerRslt)){

                $dealerResult[] = $dealerRow;
                $sum = 0;
                foreach($dealerResult['cstnoc'] as $dealerRow){
                    $sum += $dealerRow['TotalQTY'];
                }
                echo $sum;
        }
    }

AbraCadaver · Accepted Answer · 2018-04-09T15:01:44.293

2

Why are you looping $skuResult, you never use $skuRow? You could probably SUM this in the query, but for PHP:

while($dealerRow = odbc_fetch_array($dealerRslt)){
    if(!isset($dealerResult[$dealerRow['cstnoc']])) {        
        $dealerResult[$dealerRow['cstnoc']] = 0;
    }
    $dealerResult[$dealerRow['cstnoc']] += $dealerRow['TotalQTY'];
}

Then you can loop to display:

foreach($dealerResult as $cstnoc => $total) {
    echo "$cstnoc = $total";
}

Your query shows lowercase cstnoc but your result array shows Uppercase CSTNOC, so use whichever it really is.

edited Apr 09 '18 at 15:01

answered Apr 09 '18 at 14:58

AbraCadaver

78,200
7
66
87

Think column names are all upercase. – Nigel Ren Apr 09 '18 at 15:00
Your first version was fine, you actually don't need to check if an array element exists before adding a value to it (no error/warning afaik), but maybe it makes the intent clearer. – xander Apr 09 '18 at 15:03
@NigelRen: They are in output but code and query shows lower. Edited. – AbraCadaver Apr 09 '18 at 15:03
Just when they use `$DB2Conn`, I assume it's a DB2 connection, from what I've read it always returns column names in uppercase, but as long as OP is aware then it's OK. – Nigel Ren Apr 09 '18 at 15:06
Sorry for the confusion @AbraCadaver I do use the skuResult but it's higher in the script and I didn't paste it in here. Basically, I do that do perform the db2 query per sku. But this solution looks great, I'm giving it a go now – Geoff_S Apr 09 '18 at 15:09
@xander: No, check again. – AbraCadaver Apr 09 '18 at 15:12

score 1 · Answer 2 · answered Apr 09 '18 at 14:50

1

You are using the same variable $dealerRow twice. On:

while($dealerRow = odbc_fetch_array($dealerRslt)){

and in

foreach($dealerResult['cstnoc'] as $dealerRow){

try using another name inside the foreach.

answered Apr 09 '18 at 14:50

yondemon

51
5

score 0 · Answer 3 · answered Apr 09 '18 at 14:58

0

Simple foreach will do the trick.

$new = array();
foreach($arr as $k=>$v){
    $new[$v['CSTNOC']] = isset($new[$v['CSTNOC']]) ? $new[$v['CSTNOC']] + $v['TOTALQTY'] : $v['TOTALQTY'];
}
print_r($new);

Demo

answered Apr 09 '18 at 14:58

Bhaskar Jain

1,651
1
12
20

Sum within a foreach loop based on array with different group

3 Answers3