tjhilder Posted May 30, 2006 Share Posted May 30, 2006 Hi,I have this query:[code]$query = "SELECT UNIX_TIMESTAMP(date) AS udate, blog.*, COUNT(blog_id) AS blog_count FROM blog GROUP BY date ORDER BY date DESC";[/code]this statement is meant to create a list of archives like this...March 2006 (7)February 2006 (3)January 2006 (2)etc, but since the date is in datetime format --> 0000-00-00 00:00:00 it can't GROUP BY properly, I used to use another field that would display 'may 2006' etc in the news marked as that month (which worked fine) but I want to be able to group them using 'date'.I'm not sure how to go about this, so that it uses the first 0000-00.. etc as a way of grouping, but I'm not sure if this is possible and if it is, how I do it.could someone please help me?thanks in advance.--Terry Link to comment https://forums.phpfreaks.com/topic/10755-need-help-with-unix_timestamp-with-group-by/ Share on other sites More sharing options...
fenway Posted May 30, 2006 Share Posted May 30, 2006 You may want to try grouping by "EXTRACT( YEAR_MONTH FROM date )"; obviously, you could use a pretty DATE_FORMAT() call for the output itself. Incidentally, date is a reserved keyword, so be careful.Hope that helps. Link to comment https://forums.phpfreaks.com/topic/10755-need-help-with-unix_timestamp-with-group-by/#findComment-40176 Share on other sites More sharing options...
tjhilder Posted May 30, 2006 Author Share Posted May 30, 2006 thanks alot, I managed to get it working with DATE_FORMAT().thanks again! [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /] Link to comment https://forums.phpfreaks.com/topic/10755-need-help-with-unix_timestamp-with-group-by/#findComment-40380 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.