CODEWITHSUNDEEP
mysql
samjhana joshi
samjhana joshi

Reputation: 2015

how to sort months(used to categorise) taken from database column using sql query

In one of my project I am grouping my data based on month name as January, February. In front page I want a drop down with only the name of months present in database and I want in order as January, February.How do i get this using sql query.

$res = $this->db->query('SELECT date FROM tbl_name WHERE category_id="'.$data->category_id.'" GROUP BY date ORDER BY date ASC')->result_array();

here, date is column name in database which stores name of month, $data->category_id is some specific value

Any help and/or suggestions is welcome. Thanks in advance.

Upvotes: 0

Views: 526

Answers (1)

fancyPants
fancyPants

Reputation: 51928

UPDATE: Overlooked %M in the manual. It works this way:

ORDER BY month(str_to_date(`date`, '%M'))

end of update

You can have a try with

ORDER BY month(str_to_date(`date`, '%b'))

Although this just worked for me with 'Jan' for example, not with 'January'.

The other way which works for sure but is a bit more clumsy is

ORDER BY CASE `date` 
WHEN 'January' THEN 1
WHEN 'February' THEN 2
...
ELSE 0 END

Upvotes: 1

Related Questions