Mysql count within array

i have a table (tbl_operations) with rows of values(sometimes arrays). I want to get to count of each OpId for each month. I am trying through queries but in vain

from this view

to this

I am stuck here. can someone enlight me please. Or maybe use php to display the result.

SELECT tbl_operations.OpId,
                 tbl_operations.OpDate ,
                 COUNT(tbl_operations.OpId) AS `count`
            FROM tbl_operations
            WHERE MONTH(OpDate)=1
            GROUP BY  tbl_operations.OpId

>Solution :

Here’s a quick example. The first part just creates an array of arrays which simulates what you’d get from the database.

The gist is that $counts is an array with a unique OpID for a keys. The values for those arrays are sub-arrays with keys of the month and values of how many times they are found.

Display should just be a simple loop again, however you might want to sort this.

$rows = [
['3',   '2022-01-03'],
['5,3', '2022-01-15'],
['4',   '2022-01-27'],
['5',   '2022-02-01'],
['7',   '2022-02-09'],
['3,2', '2022-01-16'],
];

$counts = [];
foreach($rows as $row){
    $ids = explode(',', $row[0]);
    $month = date('m', strtotime($row[1]));
    foreach($ids as $id){
        if(!array_key_exists($id, $counts)){
            $counts[$id] = [];
        }
        if(!array_key_exists($month, $counts[$id])){
            $counts[$id][$month] = 0;
        }
        
        $counts[$id][$month]++;
    }
}

Demo here: https://3v4l.org/mVaBB

edit

From @mickmackusa, you can shorten the inner loop by using isset:

        if(!isset($counts[$id][$month])){
            $counts[$id][$month] = 0;
        }

See their comment for a demo link