Sorting dates correctly

[Andy17]

Hey guys,

 

I am outputting dates on my website in the following format: [month] [day], [year] (example: October 10, 2008). I am using the following MySQL query to pull them out of my table:

 

<?php

$ptq = "SELECT * FROM table WHERE username = '$username' ORDER BY date";
$ptresult = mysql_query($ptq);

?>

 

The thing is that the last part (ORDER BY date) does not sort the dates correctly due to my format. Let's say that I would like to sort the following dates:

 

January 27, 2008

January 28, 2007

January 26, 2008

October 10, 2008

October 10, 2007

July 15, 2008

 

I would like to have the above displayed in the following order:

 

January 28, 2007

October 10, 2007

January 26, 2008

January 27, 2008

October 10, 2008

 

Any ideas on how to do this?

 

Thank you very much in advance!

 

PS - If it's any help (I don't see why it would be), this is how I find the date before inserting it into my table:

 

<?php

$date = date(F) . " " . date(j) . ", " . date(Y);

?>

[sh0wtym3]

Make sure your column type is set to "date" (not varchar or anything else)

 

Also, try grabbing the current date by using:

$date = date("Y-m-d");

[PFMaBiSmAd]

Mysql has a DATE type for a reason. It allows you to store a date in 3 bytes (your format takes up to 18 bytes), it allows you to directly sort and compare dates, and it allows you to use about 20-30 built in mysql date functions in a query to do about anything you can think of with a date.

 

To insert the current date into a DATE type, use the mysql CURDATE() function in the query. To retrieve a DATE type in any format you want, use the mysql DATE_FORMAT() function in the query.

 

A DATE type will take less storage and will let queries execute faster than using any other data type to store dates.

[Andy17]

Well yeah, just didn't (don't, but will Google it) know how to convert the date stored in the MySQL table to the one I want to display. That's why I just used VARCHAR.