php - mySQL 查询不返回日期

Question

SELECT COUNT(*) AS count, state, DATE_FORMAT( '%m/%d', registered ) AS date_formatted
FROM users
WHERE CAST( registered AS DATE ) > DATE_SUB( CURDATE(), INTERVAL 7 DAY)
GROUP BY state, date_formatted

上面是我们用来从数据库返回信息的查询。

我们的日期保存为:2011-03-28 14:36:48(日期时间字段)

返回的数据如下:

Array
(
    [] => Array
        (
            [act] => 1
            [nsw] => 1
            [nt] => 1
            [qld] => 3
            [sa] => 1
            [tas] => 1
            [vic] => 1
            [wa] => 4
        )

)

并且它没有返回数组中的日期。

while( $row = mysql_fetch_array( $query )) { $my_array[$row['date_formatted']][$row['state']] = $row['count']; }

这是我们用来形成数组的 php 代码。

谢谢 :)

Answer 1

DATE_FORMAT将日期作为第一个参数，将格式作为第二个参数:

SELECT COUNT(*) AS count, state, DATE_FORMAT(registered ,'%m/%d')
...