Filter records by Month / Year Codeigniter

Question

Filter records by Month / Year Codeigniter

Navigation

#1 by (0 votes)

0

I have the following SQL structure:

CREATE TABLE IF NOT EXISTS 'noticia' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'id_categoria' int(11) DEFAULT NULL,
  'titulo' varchar(255) NOT NULL,
  'previa' text NOT NULL,
  'descricao' text,
  'imagem' varchar(255) NOT NULL DEFAULT '',
  'data' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'views' int(11) DEFAULT '0',
  'youtube' varchar(255) DEFAULT NULL,
  'ativo' tinyint(4) NOT NULL,
  PRIMARY KEY ('id','ativo')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I would like to create a SQL that will query the records, grouping month and year. that appears as follows:

How can I group MONTH / YEAR to build this listing? To display, I develop normal, my question is only in SQL.

I thought about doing:

SELECT * FROM noticia GROUP BY MONTH(data);

If I do this way, you will group only by month, and if you have next month the same month, you will also group. And that is not the intention. Thanks!

sql codeigniter

asked by anonymous 02.11.2015 / 15:47

1 answer

Dialog with dataTable is not displayed Using ng-view and routeProvider does not render the partial html

score 0 · Accepted Answer

I was able to find a solution. If anybody needs it, it's there.

SELECT 
    EXTRACT(MONTH FROM data) AS mes, 
    EXTRACT(YEAR FROM data) AS ano, 
    COUNT(id) AS total_noticia, 
    (CASE month(data) 
        when 1 then 'Janeiro'
        when 2 then 'Fevereiro'
        when 3 then 'Março'
        when 4 then 'Abril'
        when 5 then 'Maio'
        when 6 then 'Junho'
        when 7 then 'Julho'
        when 8 then 'Agosto'
        when 9 then 'Setembro'
        when 10 then 'Outubro'
        when 11 then 'Novembro'
        when 12 then 'Dezembro'
    END) AS mes_extenso
FROM 
    noticia 
GROUP BY 
    mes