Posted by Oli Filth on 10/13/33 11:35

gerg said the following on 24/12/2005 10:00:
> In my database I've got calendar table with the following fields,
> `month`, `day`,`year`.
>
> My sql query is
>
> $sql="SELECT * FROM `calendar` ORDER BY `year`,`month`,`day` ASC";
>
> This query puts the oldest "years" at the top, IE 2005 at the top 2007
> at the bottom, but then it alphabatizes the month. Is there any way to
> get the query to order them in the actual month order, IE don't put
> April above February during an ordering. The months are input into the
> database with the full month name IE March or December.
>

This is why DATE fields were invented (assuming you're using MySQL) - so
that you don't have to maintain three fields, and so that you don't get
problems like this.
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

See also:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

It also saves space - a DATE field is 3 bytes, whereas with your scheme
(assuming `year` and `day` are INTs, and `month` is a VARCHAR with
average length of 6.25 characters), then on average that's 15.25 bytes.


P.S. "IE" means Internet Explorer to most people in these newsgroups,
you should use "ie" or "i.e." instead. It took a few minutes to work out
that you weren't talking about IE at all... ;)

--
Oli

[Back to original message]