Mysql count within array-CodePudding

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

OpId	OpDate
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

to this

OpId	count	Month
2	1	01
3	3	01
4	1	01
5	1	01
5	1	02
7	1	02

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

CodePudding user response：

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

CodePudding user response：

If you're going to query the data in PHP, you might as well return a better result to work with in the first place:

SQL

SELECT GROUP_CONCAT(OpId), MONTH(OpDate)
FROM tbl_operations
GROUP BY MONTH(OpDate)

PHP

// Result from MySQL query
$rows = [
    ['3,5,3,4,3,2', 1],
    ['5,7', 2]
];

And you can perform a count of those grouped results like this:

$results = [];
foreach ($rows as $row) {
    $counts = array_count_values(explode(',', $row[0]));
    $results[$row[1]] = $counts;
}

Result

Array
(
    [1] => Array
        (
            [3] => 3
            [5] => 1
            [4] => 1
            [2] => 1
        )

    [2] => Array
        (
            [5] => 1
            [7] => 1
        )

)

What you really want to do though is normalise your data, then you can do this easily in SQL alone.