Pfthb

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.

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

Found solution. I had to use a helper variable ($indexhelp)

 $indexhelp=0;
 for($month_counter=1;$month_counter<=12;$month_counter++) 
 if($month_counter==$query5[$indexhelp][0]) 
 echo $query5[$indexhelp][1].',';
 $indexhelp++;
 
 else echo '0,';
 

The result is added to a variable like

//this is javascript
chart_data=[<?php CODE HERE ?>];

Also, a friend told me that most logic is convenient to use in client-side, because a complex query can overload the database server.