Not displaying in date order

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
cturner
Forum Contributor
Posts: 153
Joined: Sun Jul 16, 2006 3:03 am
Location: My computer

Not displaying in date order

Post by cturner »

I am wanting to display the table dates in date order. When I test the following mysql query it just doesn't display in the order that I want:

Code: Select all

$query = mysql_query("SELECT title, DATE_FORMAT(date_entered, '%W, %M %e, %Y') as date_entered FROM `mediareleases` ORDER BY date_entered ASC") or die("Could not query because: ".mysql_error());
This is the way it displays:
Monday, February 4, 2008
Monday, July 9, 2007
Monday, November 12, 2007
Monday, September 17, 2007

I want it to display in this order:
Monday, February 4, 2008
Monday, November 12, 2007
Monday, September 17, 2007
Monday, July 9, 2007

My table structure is:
id // the auto incremented primary key number
title
body
date_entered // type: date
Can someone please tell how I can get the query to display in the date order I want? Thanks in advance.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Not displaying in date order

Post by s.dot »

You are comparing the date as a string.

Monday, February
Monday, July
Monday, November
Monday, September

F, then J, then N, then S... see a pattern? :-D
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
cturner
Forum Contributor
Posts: 153
Joined: Sun Jul 16, 2006 3:03 am
Location: My computer

Re: Not displaying in date order

Post by cturner »

I see now what you mean, but how do I display the dates in the order I want?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Not displaying in date order

Post by s.dot »

What format is date_entered in?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
cturner
Forum Contributor
Posts: 153
Joined: Sun Jul 16, 2006 3:03 am
Location: My computer

Re: Not displaying in date order

Post by cturner »

I think you mean type. Anyway the date_entered column is date.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Not displaying in date order

Post by s.dot »

Code: Select all

SELECT title, DATE_FORMAT(date_entered, '%W, %M %e, %Y') as `date_formatted` FROM `mediareleases` ORDER BY date_entered ASC
Try that. When displaying the dates, use date_formatted.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply