Nick,
Tuesday, May 07, 2002, 4:01:09 PM, you wrote:
>> What is your column type? Why you don't use ORDER BY your_date_column
>> in the SELECT statement? It should work, but I can say you exactly
>> because I don't know your table structure. The other causes of wrong
>> result might be that something is not correct in your SQL statement.
NW> Right. here is the db schema: Feel free to berate me if I'm missing the
NW> obvious :-)
NW> CREATE TABLE iaadiplom_timetable
NW> (
NW> id INT NOT NULL AUTO_INCREMENT,
NW> module ENUM('0', '1', '2', '3', '4', '5'),
NW> week VARCHAR(255) NULL,
NW> date DATE NOT NULL,
NW> ektione VARCHAR(255) NULL,
NW> title TEXT NULL,
NW> teacher_id INT NULL,
NW> PRIMARY KEY(id)
NW> )
NW> and here is my select:
NW> $qry="SELECT id, module, week, ";
NW> $qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, ";
^^^^^^^^^^^^^^^^
The result of DATE_FORMAT() function is a string. So, in ORDER BY
clause is used string 'date'. That is why you get "wrong" sorting. Use
another alias for your date column, it should help you, i.e.:
$qry="SELECT id, module, week, ";
$qry.="DATE_FORMAT(date, \"%d %m %Y\") as mydate, ";
[skip]
$qry.="ORDER BY date ASC";
NW> $qry.="ektione, title, teacher_id ";
NW> $qry.="FROM $this->table ";
NW> $qry.="WHERE module = '$module' ";
NW> $qry.="ORDER BY date ASC";
NW> Thanks very much for the help!
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ Victoria.Reznichenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
