Jump to content


Photo

need help with UNIX_TIMESTAMP() with GROUP BY


  • Please log in to reply
2 replies to this topic

#1 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 30 May 2006 - 03:47 AM

Hi,

I have this query:
$query = "SELECT UNIX_TIMESTAMP(date) AS udate, blog.*, COUNT(blog_id) AS blog_count FROM blog GROUP BY date ORDER BY date DESC";

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 May 2006 - 05:04 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 30 May 2006 - 09:45 PM

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\" /]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users