php - 如何在 MySQL 和 PHP 中订购月份名称？

Question

我有一个显示我的数据库的查询:

    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')

我的存档表在提交时只包含数据的月份和年份。例如:

Jan 2014, Feb 2014, Mar 2014

但是我页面上显示的是:

Feb 2014, Jan 2014, Mar 2014

这是我的 php 代码，用于将我的网络中的数据插入数据库:

$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']);

代码一切正常，但是如何按月排序呢？因为我已经尝试使用 MONTH(date) 或 date.(MONTH) 它不起作用。提前致谢;)

Answer 1

您需要先将字符串“archieve”转换为日期，然后按值排序。试试这个:

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