Jump to content


Photo

Date problem , ORDER BY


  • Please log in to reply
3 replies to this topic

#1 JohneeMac

JohneeMac
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 11 January 2006 - 03:35 PM

Hi there, i am trying to display some news with the most recent date at the top

heres my SQL:

$sql = mysql_query("SELECT *,
   date_format(article_date, '%D %M, %Y') as article_date 
   FROM news_system
   ORDER BY article_date 
   ASC LIMIT 5");

It seems to mess up and have a random order of things.

Thx.

#2 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 11 January 2006 - 04:08 PM

[!--quoteo(post=335531:date=Jan 11 2006, 10:35 AM:name=JohneeMac)--][div class=\'quotetop\']QUOTE(JohneeMac @ Jan 11 2006, 10:35 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hi there, i am trying to display some news with the most recent date at the top

heres my SQL:

$sql = mysql_query("SELECT *,
   date_format(article_date, '%D %M, %Y') as article_date 
   FROM news_system
   ORDER BY article_date 
   ASC LIMIT 5");

It seems to mess up and have a random order of things.

Thx.
[/quote]

It most certainly will. The mysql engine sees the dates like this (12.10.2005 -> 12102005, 24.12.1856 -> 24121856) so 24121856 is definetly bigger then 12102005.

The date format should be arranged first by year, then by month and then date. Or maybe use TIMESTAMP() instead.

#3 fenway

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

Posted 11 January 2006 - 04:20 PM

See the reponse to [a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=82577\" target=\"_blank\"]this thread[/a] for the answer.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 JohneeMac

JohneeMac
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 11 January 2006 - 05:05 PM

Many Thanks.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users