MySQL Custom Order

In one of my projects, I need to sort queries based on a custom pre-ordered list, e.g sort query by Indonesian month. Suppose we have a table like this:

id tahun bulan jumlah
1 2011 Jan 100
2 2011 Mar 110
3 2011 Apr 120
4 2011 Jun 110
5 2011 Feb 130
6 2011 Mei 120
7 2011 Jul 110
8 2011 Des 140
9 2011 Sep 120
10 2011 Okt 130
11 2011 Nov 120
12 2011 Agt 150

 

 

 

 

 

 

 

 

 

 

 

 

I want to show the data ordered by bulan in the order of: Jan-Feb-Mar-Apr-Mei-Jun-Jul-Agt-Sep-Okt-Nov-Des. It’s clearly impossible to use direct order by bulan, so we need a work around here. After some googling around, I found a clue in StackOverflow here.

So, based on that answer, I then compiled a new variable on my config.php as below:

$bulans = array(‘Jan’,’Feb’,’Mar’,’Apr’,’Mei’,’Jun’,’Jul’,’Agt’,’Sep’,’Okt’,’Nov’,’Des’);

$order_bln = “ORDER by CASE bulan\n”;

foreach($bulans as $key => $val){

    $order_bln .= ‘WHEN \” . $val . ‘\’ THEN ‘ . $key . “\n”;

}

$order_bln .= “\nEND”;

To use $order_bln in a query is simple, just add it in the end of query variable, like this:

$qry = “select * from stok where tahun=’$tahun’ $order_bln”;

 

No Comments »

RSS feed for comments on this post. TrackBack URL


Leave a Reply

*

Skip to toolbar