Sort by date PHP/MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thedarkdestroyer
Forum Newbie
Posts: 9
Joined: Thu Sep 28, 2006 9:27 am

Sort by date PHP/MySQL

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
thedarkdestroyer
Forum Newbie
Posts: 9
Joined: Thu Sep 28, 2006 9:27 am

Date Format in a Table

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

try:

Code: Select all

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