For example, I have the following data in my dB database data

and I want to count the entries grouping them by month. In addition, I want to display a count of 0 for those months which don't exist in the dB.

Here's my snippet of code using Codeigniter:

$result = $builder->select('COUNT(insta_alert_id), MONTH(created_at)')
            ->where("created_at BETWEEN '2022-04-01' AND '2023-07-31'")
            ->where('school_id', $loginDt['school_id'])
            ->groupBy('YEAR(created_at)')
            ->groupBy('MONTH(created_at)')
            ->get()
            ->getResultArray();

        $data = array();

        if (!empty($result)) {
            $rowDt = array();
            foreach ($result as $dt) {
                $rowDt['count'] = $dt['COUNT(insta_alert_id)'];
                $rowDt['month'] = $dt['MONTH(created_at)'];
                $data[] = $rowDt;
            }
        }

And I get the following result in Postman:

"data": [
    {
        "count": "11",
        "month": "4"
    },
    {
        "count": "6",
        "month": "5"
    },
    {
        "count": "1",
        "month": "6"
    }
]

And what I want is:

"data": [
        {
            "count": "0",
            "month": "1",
        },
        {   "count": "0",
            "month": "2"
        },
        {
            "count": "0",
            "month": "3"
        {
            "count": "11",
            "month": "4"
        },
        {
            "count": "6",
            "month": "5"
        },
        {
            "count": "1",
            "month": "6"
        }
        .
        .
        .
    ]

CodePudding user response：

You can fill in missing months:

1. Gather all filled months
2. Run through 12 months and fill in blanks

$existingMonths = array_reduce(
    $data,
    function ($months, $item) {
         $months[$item['month']] = $item['month'];

         return $months;
    },
    []
);

for ($i = 1; $i <= 12; $i  ) {
    if (!isset($existingMonths[$i])) {
        $data[] = [
            'count' => 0,
            'month' => $i,
        ];
    }
}