List every month in an intevalo, or in the query or in the programming?

Question

I’m making a chart that lists a certain amount per month, but can have mès that has nothing, with this returns me no value and does not list in the chart.

I don’t know if I should make the change in the query or programming, can anyone help me?

My query looks like this:

SELECT DATE_FORMAT(dt_validade, '%y-%m') as anoMes, count(*) as qtde
FROM tabela
WHERE dt_validade > '{$today}'
GROUP BY anoMes
ORDER BY anoMes

her return is more or less that

anoMes  | qtde
14-08   | 2
14-09   | 5
14-12   | 10
14-12   | 10
14-12   | 10
15-01   | 1
15-03   | 1

and with that, in php I run a loop in the result, I play the dice for an array that sends a json_encode for the data to the graph, but with this will only show me in the graph data of the months 08, 09 and 12, but I need to show also of the months 10 and 11 with zero values.

And in short, what I did in programming was this:

$cols = array(
    array(
        'Período', 
        'Quantidade'
    )
);
$rows = array();

$query = "A query passada";    
$result = $query->result();
foreach ($result as $r) {
    array_push($rows, array(
        $r->mesAno, 
        (int) $r->qtde
    ));
}    

$dados = array_merge($cols, $rows);     

return json_encode($dados);

And as a suggestion given to me, the expected result is this:

anoMes  | qtde
14-08   | 2
14-09   | 5
14-10   | 0
14-11   | 0
14-12   | 10
15-01   | 1
15-03   | 1

Follow link with build on SQLFIDDLE, so I guess I can get a little clearer

Answer 1

It wasn’t the most beautiful thing in the world, but it worked:

$rows = array();

$query = "SELECT DATE_FORMAT(dt_validade, '%m-%y') as anoMes, count(*) as qtde
          FROM tabela
          WHERE dt_validade > now()
          GROUP BY anoMes
          ORDER BY dt_validade";    

$result = $query->result();
foreach ($result as $r) {
    $rows[$r->mesAno] = (int) $r->qtde;
}    

reset($rows);
list($mes, $ano) = explode('-', key($rows));
$current = strtotime(($ano+2000).'-'.$mes.'-01');

end($rows);
list($mes, $ano) = explode('-', key($rows));
$last = strtotime(($ano+2000).'-'.$mes.'-01');

$dates = array();
while( $current <= $last ) { 
    $dates[date('m-y', $current)] = 0;
    $current = strtotime('+1 month', $current);
}

$rows = array_merge($dates, $rows);
$dados = array( array('Período', 'Quantidade'));
foreach($rows as $key => $value) $dados[] = array($key, $value);

return json_encode($dados);

Answer 2

My knowledge in PHP is very limited, but I can try to help with a bank-side solution.

Set the period

SELECT @mindate = MIN(dt_validade), @maxdate = MAX(dt_validade) FROM tabela

Partial result:

|                    @mindate |                     @maxdate |
|-----------------------------|------------------------------|
| July, 30 2014 00:00:00+0000 | March, 15 2015 00:00:00+0000 |

Get the list of months between the two periods: Store in temporary table

CREATE TEMPORARY TABLE 
IF NOT EXISTS 
ListaAnoMes AS
SELECT Date_format(m1, '%y-%m') AS anoMes 
FROM   (SELECT ( @mindate - INTERVAL Dayofmonth(@mindate)-1 day ) + 
               INTERVAL m month AS 
                      m1 
        FROM   (SELECT @rownum := @rownum + 1 AS m 
                FROM   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4, 
                       (SELECT @rownum :=- 1) t0) d1) d2 
WHERE  m1 <= @maxdate 
ORDER  BY m1; 

Partial result:

SELECT * FROM LISTAANOMES

| ANOMES |
|--------|
|  07-14 |
|  08-14 |
|  09-14 |
|  10-14 |
|  11-14 |
|  12-14 |
|  01-15 |
|  02-15 |
|  03-15 |

Cross the two tables

SELECT         B.AnoMes,
               Count(a.id) as qtde
FROM           ListaAnoMes as B
LEFT JOIN      tabela as A 
           ON  DATE_FORMAT(A.dt_validade, '%m-%y') = B.anoMes
GROUP BY       B.AnoMes

Upshot:

| ANOMES | QTDE |
|--------|------|
|  01-15 |    1 |
|  02-15 |    0 |
|  03-15 |    1 |
|  07-14 |    1 |
|  08-14 |    2 |
|  09-14 |    5 |
|  10-14 |    0 |
|  11-14 |    0 |
|  12-14 |   10 |

Answer 3

You want a date interval ?

SELECT DATE_FORMAT(dt_validade, '%m-%y') as mesAno, count(*) as qtde
FROM tabela
WHERE dt_validade > '{$today}' OR dt_validade = NULL OR dt_validade = ''
GROUP BY mesAno

I don’t know if the default is null or empty string, depending on you don’t need 2 or, only one