|
Posted by J.O. Aho on 10/13/21 11:35
gerg wrote:
> 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.
The fancy way, make another table
create table themonths(no int(2) not null,name varchar(3) not null, PRIMARY
KEY (no,name));
Where you list the months, then you do a join between the calendar and
themonths table and use themonths.no instead of calendar.month.
The more sensible way:
$sql="SELECT * FROM calendar ORDER BY
year,DATE_FORMAT(CONCAT(year,'-',month,'-',day),'%c'),day ASC";
Not sure if it works well as you have stored the month name and not the number
that is how the time functions in mysql is designed to work.
//Aho
[Back to original message]
|