Summing an array value based on other array value as key

[thenorman138]

I'm trying to build some data in an excel report through PHP but I'm still having trouble getting the array structure and foreach loops the way I need them. 

 

Basically, I'm pulling a select on Mysql, matching it to a select in DB2 ( I need values from each) and then I need to loop the results and perform some calculations and then relate it back to the highest level array for the report.

 

My first query pulls records like this:

 

    sku_id   |   customer_id   |   locations   |   group_id   |   frame   |   cover   |   color   |   start_date

 

    1             5400              2               1               1051        1150        99          2018-02-09

    1             5401              3               1               1051        1150        99          2018-02-09

 

Based on those results I pull a query on DB2 that gets every record since that start_date where those customers ordered that same sku_id so I'm getting multiple records for the sku_id/customercombo. That query returns something like this:

 

    customer   |   frame   |   cover   |   color   |      date         |   TotalQTY

 

    5400            1051          1150         99        2018-02-09           5

    5400            1051          1150         99        2018-02-19           2

    5400            1051          1150         99        2018-03-09           5

    5401            1051          1150         99        2018-02-11           1

    5401            1051          1150         99        2018-02-15           7

    5401            1051          1150         99        2018-02-25           5

 

The queries work and my arrays are printing with the proper data, but I need some guidance on structuring these right. I'm matching the SKU up by attributes like cover, color and frame.

 

 

I need to look first at each sku_id from the first query, execute the 2nd and then look at each customer to get their  total quantity of that sku. So based on the example above I would need to get a total quantity of 12 for customer # 5400 and 13 for customer #5401, both for sku 1.

 

My end goal is to get the total quantity of sku on a per dealer basis, so with the example above, my report would look like this:

 

    SKU   |   GROUP   |   TotalQTY

    -----------------------------------------

    1                   1             25

 

My array structure looks right as far as my first foreach (foreach sku, perform the query, which returns an array for each record per customer). I'm not sure how to do the foreach on the customer so that I can work on a per customer basis and get the total quantity of that sku per customer.

 

I feel like I would need to say for each dealerRslt as Customer, total all totalQTY 

 

Here's the portion of the script in question:

 

    $skuQuery = "
        SELECT 
            sku_id,
            customer_id,
            locations,
            s.sku_group_id as groupID,
            s.frame as frame,
            s.cover1 as cover,
            s.color1 as color,
            start_date - interval 7 day as start_date
        from products p
        inner join skus s on p.sku_id = s.id
        where curdate() between p.start_date and p.expire_date
        group by sku_id, customer_id";


    $skuRslt = mysqli_query($conn,$skuQuery);


    while($skuRow = mysqli_fetch_assoc($skuRslt)){
        
        $skuResult[] = $skuRow;


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


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


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


                    $dealerResult[] = $dealerRow;
                    print_r($dealerResult);


            }
        }
    }

 

[Barand]

Just curious after googling DB2 datetimes, but does

SELECT CHAR(date, ISO) as start_date

workk in place of your

SELECT cast(Left(date, 4)||'-'||substring(date,5,2)||'-'||substring(date, 7,2) as date) as start_date,

What are the table structures of the tables (products, skus, table1) you are currently using in the above code?

[thenorman138]

That didn't work actually but I was able to get closer to what I need. Printing both arrays gives me this:

 

 

 

    Array
    (
        [0] => Array
            (
                [sku_id] => 1
                [dealer_id] => 1976
                [locations] => 1
                [groupID] => 1
                [frame] => 1051
                [cover] => 1150
                [color] => 99
                [start_date] => 2018-03-
            )


        [1] => Array
            (
                [sku_id] => 1
                [dealer_id] => 5400
                [locations] => 1
                [groupID] => 1
                [frame] => 1051
                [cover] => 1150
                [color] => 99
                [start_date] => 2017-04-
            )


        [2] => Array
            (
                [sku_id] => 1
                [dealer_id] => 11316
                [locations] => 1
                [groupID] => 1
                [frame] => 1051
                [cover] => 1150
                [color] => 99
                [start_date] => 2017-02-
            )


    )
    Array
    (
        [0] => Array
            (
                [CSTNOC] => 1976
                [TOTALQTY] => 2
            )


        [1] => Array
            (
                [CSTNOC] => 5400
                [TOTALQTY] => 5
            )


        [2] => Array
            (
                [CSTNOC] => 11316
                [TOTALQTY] => 14
            )


    )

I think all I really need is to know how to structure this so that I can get a result for 

 

Sku_id[1] => 21

 

so that I know there are 21 total for that sku

[Barand]

Building the second array like this should help

 

$dealerResult = [];
foreach($skuResult as $skuRow){
            while($dealerRow = odbc_fetch_array($dealerRslt)){
                    if ( !isset($dealerResult[$dealerRow['CSTNOC']]) ) {
                        $dealerResult[$dealerRow['CSTNOC']] = 0;
                    }
                    $dealerResult[$dealerRow['CSTNOC']] += $dealerRow['TOTALQTY']];
            }
        }

[thenorman138]

That makes sense, but it seems to give me kind of an inverse of what I'm looking for, shown here:

(
    [sku_id] => 1
    [dealer_id] => 1976
    [locations] => 1
    [groupID] => 1
    [frame] => 1051
    [cover] => 1150
    [color] => 99
    [start_date] => 2018-03-02
)
Array
(
    [1976] => 2
)
Array
(
    [sku_id] => 1
    [dealer_id] => 5400
    [locations] => 1
    [groupID] => 1
    [frame] => 1051
    [cover] => 1150
    [color] => 99
    [start_date] => 2017-04-07
)
Array
(
    [5400] => 5
)
Array
(
    [sku_id] => 1
    [dealer_id] => 11316
    [locations] => 1
    [groupID] => 1
    [frame] => 1051
    [cover] => 1150
    [color] => 99
    [start_date] => 2017-02-03
)
Array
(
    [11316] => 14
)

I'm wondering how I can just get the sku_id element as the top element, without all those other items, and within that I would have all 3 of those customer => total values

[Barand]

Use your first array to create an sku array to store the totals for each sku. Then loop through array1 adding the values from array2 to the sku array.

$skuTots = array_fill_keys(array_unique(array_column($array1, 'sku_id')), 0);

foreach ($array1 as $rec) {
    $skuTots[$rec['sku_id']] += $array2[$rec['dealer_id']];
}

Edited April 9, 2018 by Barand
added array_unique()

[thenorman138]

That's the right ID for the record basis but it gives me this:

 

(

    [1] => 2

)

Array

(

    [1] => 5

)

Array

(

    [1] => 14

)

 

Is there a way to do the foreach count and then have the output be just total per sku_id like:

 

Array

(

    [1] => 21

)

 

The reason I ask, is I need to add other things in from the 2nd array eventually so the foreach loops will do things at that level but for now I'd like the output for the report to just have each sku and the totals, but keeping the current array structure

[requinix]

Trying to bring three threads into this single one.

 

 

What is your current code, all the code, and what are your problems/questions with it?