PHP：我需要在没有sql查询数据的月份填写“0”

Question

This seemed simple at first, but now it has become a headache.

I have a number of robberies for each month in a year, lets say 2016. My query

SELECT MONTH(denuncia.fecha_registro_denuncia) as mes, count(denuncia.codigo_incidente)
            FROM denuncia
            INNER JOIN incidentes ON incidentes.codigo_incidente=denuncia.codigo_incidente
            WHERE YEAR(denuncia.fecha_registro_denuncia)='".$a."' AND denuncia.codigo_incidente=0
            GROUP BY mes

denuncia is the same as "report to the police"

returns this:

+-------+--------+
| month | robber.|
+-------+--------+
|     1 |      2 |
|     2 |      2 |
|     3 |      3 |
|     4 |      2 |
|     5 |      1 |
|     8 |      2 |
|     9 |      3 |
|    10 |      1 |
|    11 |      5 |
|    12 |      2 |
+-------+--------+

The query is fetched as an PHP multidimensional array, using

while($rows=mysqli_fetch_array($result,MYSQLI_NUM)) {
    $b[]=$rows;
}

Data in array format:

Array
(
[0] => Array
    (
        [0] => 1
        [1] => 2
    )

[1] => Array
    (
        [0] => 2
        [1] => 2
    )

[2] => Array
    (
        [0] => 3
        [1] => 3
    )

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

[4] => Array
    (
        [0] => 5
        [1] => 1
    )

[5] => Array
    (
        [0] => 8
        [1] => 2
    )

[6] => Array
    (
        [0] => 9
        [1] => 3
    )

[7] => Array
    (
        [0] => 10
        [1] => 1
    )

[8] => Array
    (
        [0] => 11
        [1] => 5
    )

[9] => Array
    (
        [0] => 12
        [1] => 2
    )

)

As you can see, I am missing months 6 and 7 from my query result. Those months there were no incidents. As I searched through StackOverflow, filling the query with zeroes is somewhat a complex SQL query.

So, I have PHP to solve this. I need to output this data to a chart display library so I can view it nice and clean. The format for the input data would be:

[2,2,3,2,1,0,0,2,3,1,5,2]

So I need to put 0's on positions 6 and 7 to fill empty months with no incidents.

Thing is, my coding skills are poor. I tried some solutions but keep getting into a wall, been hours already.

Basically the algorythm is: -Go through the array -Check if month exists -If it doesn´t exist, echo '0,"; -Go next position -Check again if month exists -...

Been coding for many hours and my mind can´t grasp the solution. Thanks for any help.

Answer 1

one way you can do is you can pree fill month array with zero and set value

$month = array_fill_keys(range(1, 12),0); //fill month array key with month(1-12)

while($rows=mysqli_fetch_array($result,MYSQLI_NUM)) {
  $month [$rows[0]]= $rows[1]; //check if exists set value
}