Page 1 of 1

Sort by date PHP/MySQL

Posted: Thu Oct 05, 2006 7:10 am
by thedarkdestroyer
I have a MySQL database which has a column called "Date", i want the results to be ordered in date order so i do:

$query="SELECT * FROM complete ORDER By Date";

The dates are entered in the format DD/MM/YYYY

When the results are displayed it will only list them by oder of DD, how can i make it order them by DD/MM/YYYY


Thanks

Posted: Thu Oct 05, 2006 7:24 am
by volka
DD/MM/YYYY sounds like a varchar/text field. Why isn't it a date-type or in such a format (e.g. yyyymmdd) so that the natural string order is sufficient?

Date Format in a Table

Posted: Wed Oct 11, 2006 5:31 am
by thedarkdestroyer
When I am creating my table would a command like this be sufficient to create the Date column in date format:


create table log(ID int(10) not null auto_increment primary key, FirstName VARCHAR(30), LastName VARCHAR(30), DateField DATE dateformat( '-%d -%m -%y');

when I try to create this table, i get an error message, ......the right syntax to use near'dateformat('-%d -%m -%y'))' at line 1

Posted: Wed Oct 11, 2006 5:56 am
by volka
I don't think you can set the date format in create table, never heard of it.
But you can use date_format in a SELECT statement.

Posted: Wed Oct 11, 2006 6:14 am
by itsmani1
try:

Code: Select all

$query="SELECT `Date` as dat FROM complete ORDER By dat";