combining data from 2 arrays into final array for csv output

[thenorman138]

I'm trying to restructure my arrays so that I can get the desired output in one final array that will be used to fill a csv, eventually spreadsheet.

 

My first msyql query and the resulting array give me this structure:

 

    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-
            )
     )

 

So I get 3 records, each for the same product (sku_id) but a different customer(dealer_id). Then I match this data up in a similar table in DB2 to get quantity for each order of the sku_id. That 2nd query/array shows this:

 

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


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


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


    )

 

However, this is what I'm trying to achieve. 

 

In the below example, sku_id, groupID and location would come directly from the first query. Days is the number of days between start_date (from query 1) and curDate(). Qty is orqtyc from query 2 and Average is a calculation based on locations, days and qty. Each row is based on a different customer, so even though the below example is for one sku, the rows signify a different customer with different locations, qty, etc. 

 

  

  SKU_id    |   groupID   | locations (n) | Days (x) | Qty(q) | Average (x/(q/n))
 --------------------------------------------------------------------------------
    123              1            3           120         15           24
    123              1            2           12          6            4

 

The Report or CSV would then only show one line item for the above:

    SKU   |   Group   |   Average Days   |   Total units sold
    123   |     1     |       28         |          21   

Basically, for every sku I'm getting those records and then I need  to do those calculations by row, and get a total of those values for each sku. The report will only have one line item per sku.

 

I have everything I need to get there, but how can I structure a final array to get my desired outputs like above?

 

FULL SCRIPT:

 

        $skuQuery = "
        SELECT 
            sku_id,
            dealer_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, dealer_id
        limit 3";


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


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


        $dealerQuery = "
            SELECT
                  cstnoc,
                  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
        ";


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


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


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