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 Quote 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. Quote 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\" /] Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.