Results 1 to 4 of 4
January 17th, 2007, 12:38 AM #1Need 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.
select distinct year(xpressit_date) as year, month(xpressit_date) as month from $table where xpressit_date < now() order by xpressit_date desc
Any ideas why it works on one and not on the other?
January 17th, 2007, 01:32 AM #2Bug 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.
January 17th, 2007, 02:19 AM #3
January 17th, 2007, 03:00 AM #4
Featured: How long is your mysql query takingBy oranges in forum Programming / Datafeeds / ToolsReplies: 2Last Post: August 31st, 2011, 05:37 PM
By dflsports in forum Programming / Datafeeds / ToolsReplies: 3Last Post: October 22nd, 2006, 11:49 AM
By concorde in forum Programming / Datafeeds / ToolsReplies: 13Last Post: July 17th, 2005, 03:38 PM
By IGshop in forum Programming / Datafeeds / ToolsReplies: 15Last Post: January 19th, 2004, 08:49 AM