Results 1 to 4 of 4
  1. #1
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Need help with MySQL query
    I've redeveloped my blog from the ground up, and the last thing I'm working on is of course giving me trouble. I've got the date & time stored as datetime.

    There are currently entries from December 2006 and January 2007.

    I'm trying to get each unique month/year (for archive listing down the side).

    My current blog that's still live has a datetime field as well, and I use this exact same code and it works just fine. Which is why I never expected it to give me any trouble.


    PHP Code:
    select distinct year(xpressit_date) as yearmonth(xpressit_date) as month from $table where xpressit_date now() order by xpressit_date desc 
    On my new one, that gives me:

    January 2007
    January 2007

    Any ideas why it works on one and not on the other?

    Shawn Kerr (.com) | Disney World | SEC Football

  2. #2
    Full Member RickPlmr's Avatar
    Join Date
    July 6th, 2006
    Location
    Oregon
    Posts
    489
    Bug in newer version of MySQL
    According to this thread at http://bugs.mysql.com/bug.php?id=11484, using Select DISTINCT on a non-indexed field used to work on MySQL 4 but does not on MySQL 5.0. That might explain why it worked on your previous website, if they were running an older version of MySQL.

    Try indexing the fields, or switching the table type from MyISAM to InnoDB.

  3. #3
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Both machines have 4.3.10.

    Indexing and/or altering the table type didn't make a difference.

    Shawn Kerr (.com) | Disney World | SEC Football

  4. #4
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    Bah, late night. Had php on my mind.

    Test server has mysql 4.1.10.

    The server that the blog is live on now has 3.something.

    Shawn Kerr (.com) | Disney World | SEC Football

  5. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Featured: How long is your mysql query taking
    By oranges in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: August 31st, 2011, 05:37 PM
  2. Exporting MYSQL export query to a text file
    By dflsports in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: October 22nd, 2006, 11:49 AM
  3. How to speed up the mulit-condition query of mysql
    By concorde in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: July 17th, 2005, 03:38 PM
  4. Help please with mysql query
    By IGshop in forum Programming / Datafeeds / Tools
    Replies: 15
    Last Post: January 19th, 2004, 08:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •