sorting by date

[nubble]

hey folks :)  Got a wicked newbie question.  I've got a bunch of data to display and want to sort it by date.  Now it used to work when I was echoing the date in native format: YYYY-MM-DD - but I updated the date_format recently to display as %m/%d/%Y - now the data is sorting the dates based on their alphabetical order, not by the actual order in which they occured.

I'm using

$sql = "SELECT `snakeID`,  `quantity`, `type`, `notes`, DATE_FORMAT(date, '%m/%d/%Y') AS `date` FROM `feedings` WHERE `snakeID` LIKE '$critter' ORDER BY date DESC";

and here's an example page: [url=http://sprucenubblefarm.com/chondros/TI05J01_feedings.php]http://sprucenubblefarm.com/chondros/TI05J01_feedings.php[/url]

so the script is grouping all the 12's together, all the 11's and so on, instead of what I'm trying to get, which is a chronological order with the newest stuff on top

Can anyone help?

Thanks!
Amy

[piznac]

Well you could use gregoriantojd. Look up this function on php.net. It will take it to a set of numbers that you can sort by. Wait I dont think that will work cause then you would have to display it that way unless you had to different fields in the DB,.. sorry for the confusion

[obsidian]

I would recommend selecting your formatted date as a different column name so that when you sort by date, it still sorts by the initial date format:
[code]
SELECT `snakeID`,  `quantity`, `type`, `notes`, `date`, DATE_FORMAT(date, '%m/%d/%Y') AS `newDate` FROM `feedings` WHERE `snakeID` LIKE '$critter' ORDER BY date DESC
[/code]

Now, you are actually still ordering by the appropriate date, but you have a "newDate" column that holds the formatted date.

Hope this helps!

[nubble]

Obsidian - you friggin rule :)  I'm guessing you knew that tho.

-Amy  ;D