[SOLVED] Sorting dates properly

[Andy17]

Hey guys,

 

I am storing my dates in my MySQL table in the following format: Y-m-d (example: 2008-10-14). I use the following query to pull out the date:

 

$listq = "SELECT *, DATE_FORMAT(`date`, '%M %e, %Y') as `date` FROM jokes WHERE category = 'Blonde' ORDER BY date LIMIT {$number}, 20";
$listr = mysql_query($listq) or die(mysql_error());

 

There are obviously a few elements of that query that you don't know anything about, but that is quite irrelevant. Here is how the dates are sorted:

 

http://www.jokeheaven.eu/jokes/blonde.php

 

 

I would like the numbers to be sorted like this: 3, 2, 1 instead of 1, 2, 3. For example, the list on that page should look like this:

 

November 7, 2008

November 7, 2008

November 7, 2008

November 2, 2008

October 18, 2008

October 16, 2008

October 16, 2008

October 16, 2008

October 14, 2008

October 14, 2008

October 14, 2008

October 14, 2008

 

Also, the months are not going to be sorted correctly soon with my currently query, I believe. Any ideas? I hope I explained myself well enough for you to get my point.

 

 

Thanks a lot in advance,

Andy

[php.ajax.coder]

Try adding DESC see code:

 

SELECT * FROM Persons ORDER BY LastName DESC

[PFMaBiSmAd]

Pick a different alias name for the DATE_FORMAT() as _____. The ORDER BY date is ordering by the formatted date string, not the original date column.

[Andy17]

Pick a different alias name for the DATE_FORMAT() as _____. The ORDER BY date is ordering by the formatted date string, not the original date column.

 

Hello PFMaBiSmAd and thank you for your reply,

 

If I change it, my dates are not formatted correctly. However, it might be me who misunderstood you because I am not too experienced with the more advanced features in MySQL. If you could give me an example, I would very much appreciate it.

 

Sorry for the inconvenience.

 

 

php.ajax.coder: I had DESC in my query beforehand but they were not sorted correctly either.

[runnerjp]

i belive DATE_FORMAT is an allready taken name so when you are desc them your dec the DATE_FORMAT function and not your query!

[Daniel0]

What PFMaBiSmAd means it that you should do this instead:

 

$listq = "SELECT *, DATE_FORMAT(`date`, '%M %e, %Y') as `date_formatted` FROM jokes WHERE category = 'Blonde' ORDER BY date DESC LIMIT {$number}, 20";

 

Then use date_formatted in your app when you need the formatted date.

[Andy17]

Then use date_formatted in your app when you need the formatted date.

 

Sorry, I'm not entirely sure what you mean by that. I'm quite the noob, I know.

[Daniel0]

Well, you know when you've fetched the result using e.g. mysql_fetch_assoc() then you'll get an associative array where the keys are the field names? Before where you used date as key you'd just use date_formatted because you setup an alias in the query.

 

I.e.: $item['date_formatted'] instead of $item['date'].

[Andy17]

Well, you know when you've fetched the result using e.g. mysql_fetch_assoc() then you'll get an associative array where the keys are the field names? Before where you used date as key you'd just use date_formatted because you setup an alias in the query.

 

I.e.: $item['date_formatted'] instead of $item['date'].

 

You, Sir, are a genius. Thank you very much for your help.