mySQL query not returning dates

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

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

I cannot believe that for query with 3 expressions in SELECT clause you get 8 values in php. It is just impossible. PS: CAST( registered AS DATE ) casting here is pointless and declines the possibility of using indexes. — zerkms
– zerkms, Commented Mar 29, 2011 at 5:42

The Scrum Meister · Accepted Answer · 2011-03-29 05:37:26Z

3

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

answered Mar 29, 2011 at 5:37

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mySQL query not returning dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related