How to grouping data from MySQL and make it multi-dimensional array PHP-CodePudding

I have some data in the database that records the stocks of items and I want to group that data based on month-year for every item.

It looks like this for items :

item_id | item    |
     1  | item_1  |
     2  | item_2  |
     3  | item_3  |
     4  | item_4  |

And for stocks :

stock_id | item_id  |  date       |  amount
    1    | 1        |  2022-03-01 |  100
    2    | 2        |  2022-03-01 |  120
    3    | 3        |  2022-03-01 |  100
    4    | 4        |  2022-03-01 |  400
    5    | 1        |  2022-04-01 |  100
    6    | 2        |  2022-04-01 |  120
    7    | 3        |  2022-04-01 |  100
    8    | 4        |  2022-04-01 |  400
...

What I need to achieve is something like this :

$data = [
    [
      'y' => '03',
      '1' => 100,
      '2' => 120,
      '3' => 100,
      '4' => 400
    ],
    [
      'y' => '04',
      '1' => 100,
      '2' => 120,
      '3' => 100,
      '4' => 400
    ],
];
...

A simple query that I've tried so far :

SELECT MONTH(date) as Month, SUM(amount) as Amount, item
FROM stocks as s
LEFT JOIN items as i
ON s.item_id = i.item_id
GROUP BY s.id_item, MONTH(date)
ORDER BY MONTH(date) ASC

DB Fiddle

How to grouping that data by month and sum the amount of every item in every month?

UPDATED PHP SNIPPET

$StockData = [];

foreach ( $StockModel->stockMonthly()->getResult() as $stock )
{
    $StockData[] = [
        'y' => $stock->Month
    ];

    for ( $i = 0; $i < count($StockData); $i   )
    {
        if ( $StockData[$i]['y'] === $stock->Month)
        {
            array_push($StockData[$i], [$stock->item => $stock->Amount]);
        }
    }
}

I don't know how to grouping the data into the same month

CodePudding user response：

Nice to see a reproducible example. For this, you can collect all the item IDs on the month key in an associative array and later add that single entry of month at the top using array_merge like below:

<?php

$StockData = [];

foreach ( $StockModel->stockMonthly()->getResult() as $stock ){
    $StockData[ $stock->Month ] = $StockData[ $stock->Month ] ?? [];
    $StockData[ $stock->Month ][ $stock->item ] = $stock->Amount;
}

$result = [];

foreach($StockData as $month => $monthlyData){
    $result[] = array_merge(['y' => $month], $monthlyData);   
}

print_r($result);

CodePudding user response：

You can use the query:

select right(concat('0',month(`date`)),2) as "y", 
  sum(if (item_id=1,amount, null)) as "1",
  sum(if (item_id=2,amount, null)) as "2",
  sum(if (item_id=3,amount, null)) as "3",
  sum(if (item_id=4,amount, null)) as "4"
from stocks
group by right(concat('0',month(`date`)),2)

In case you have a variable number of item_id's, you are better off by building the array in PHP from a direct data query.