CODEWITHSUNDEEP
phpmysql
Latox
Latox

Reputation: 4695

mySQL query not returning dates

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

Above is our query we are using to return information from our database.

Our dates are saved as: 2011-03-28 14:36:48 (datetime field)

The data being returned is as follows:

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

)

And it isn't returning the date in the array.

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

Here is the php code we are using to form the array.

Thanks :)

Upvotes: 0

Views: 90

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

DATE_FORMAT takes the date as the first argument, and the format as the second argument:

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

Upvotes: 3

Related Questions