CODEWITHSUNDEEP
phpmysqlsql
ulaaaan
ulaaaan

Reputation: 76

How to ordering name of month in MySQL & PHP?

I have a query for showing my database :

    SELECT archieve, SUM(items_in) AS income, SUM(items_out+black_out+white_out) AS outcome, SUM((ball_out+black_out+white_out
    )-items_in) AS efficiency, SUM((items_in / ( ball_out + black_out + white_out))*100) AS percent 
    FROM items 
    GROUP BY archieve
ORDER BY DATE_FORMAT(archieve,'%m')

my archieve table just content month and year of data when it's submitted. For example :

Jan 2014, Feb 2014, Mar 2014

but what showed in my page is :

Feb 2014, Jan 2014, Mar 2014

This my php code to insert data from my web into database :

$date_trans = date("d M Y");
$archieve   = date("M Y");
$items_in   = mysql_real_escape_string($_POST['items_in']);
$ball_out   = mysql_real_escape_string($_POST['ball_out']);
$black_out  = mysql_real_escape_string($_POST['black_out']);
$white_out  = mysql_real_escape_string($_POST['white_out']);
$ball   = mysql_real_escape_string($_POST['ball']);
$black  = mysql_real_escape_string($_POST['black']);
$white  = mysql_real_escape_string($_POST['white']);
$note   = mysql_real_escape_string($_POST['note']);

Everything's fine with the code, but how to make it ordered well by month? Because I've tried with MONTH(date) or date.(MONTH) it doesn't work. Thanks in advance ;)

Upvotes: 1

Views: 69

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25361

You need to convert your string "archieve" to date first, and then sort by the value. Try this:

ORDER BY STR_TO_DATE(archieve, '%M %Y')

Upvotes: 1

echo_Me
echo_Me

Reputation: 37253

try this

   ORDER BY FIELD(archieve,'Jan','Feb','Mar',...)

or this

  ORDER BY FIELD(DATE_FORMAT(archieve,'%m'),'01','02','03',...)

Upvotes: 0

Related Questions