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

Question

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

mighty_dev · Accepted Answer · 2022-09-09 10:06:06Z

2

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

answered Sep 9, 2022 at 10:06

mighty_dev

17.8k3 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slaakso · Accepted Answer · 2022-09-09 09:36:01Z

0

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.

edited Sep 9, 2022 at 9:36

answered Sep 9, 2022 at 7:18

slaakso

9,1452 gold badges19 silver badges29 bronze badges

1 Comment

MSI Over a year ago

From my simple SQL I could be able to retrieve the data, but now how to create the correct multidimensional array as I mentioned above.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related